sql 列行转换语句

[lijianqiang]

有如下表
标号 任务单号 合同号 盘次 材料名称 规格 用量
C30 2013-000002 HT2013001007 4 废石 1999
C30 2013-000002 HT2013001007 4 水泥 706
C30 2013-000002 HT2013001007 4 粉煤灰 238
C30 2013-000002 HT2013001007 4 矿粉 251
C30 2013-000002 HT2013001007 4 水 269
C20 2013-000002 HT2013001007 4 外加剂 26.5
C20 2013-000002 HT2013001007 0 砂 10860
C20 2013-000002 HT2013001007 0 碎石 3727
C20 2013-000002 HT2013001007 0 废石 8154

如何得到如下结果
标号 废石 水泥 粉煤灰 矿粉 水 外加剂 砂 碎石
C30 1999 706 238 251 269 0 0 0
C20 8154 0 0 0 0 26.5 10860 3727

求语句

[Mr_Nice]

if object_id('[TB]') is not null drop table [TB]
go
create table [TB] (标号 nvarchar(6),任务单号 nvarchar(22),合同号 nvarchar(24),盘次 int,材料名称 nvarchar(6),规格 decimal(9,1),用量 sql_variant)
insert into [TB]
select 'C30','2013-000002','HT2013001007',4,'废石',1999,null union all
select 'C30','2013-000002','HT2013001007',4,'水泥',706,null union all
select 'C30','2013-000002','HT2013001007',4,'粉煤灰',238,null union all
select 'C30','2013-000002','HT2013001007',4,'矿粉',251,null union all
select 'C30','2013-000002','HT2013001007',4,'水',269,null union all
select 'C20','2013-000002','HT2013001007',4,'外加剂',26.5,null union all
select 'C20','2013-000002','HT2013001007',0,'砂',10860,null union all
select 'C20','2013-000002','HT2013001007',0,'碎石',3727,null union all
select 'C20','2013-000002','HT2013001007',0,'废石',8154,null

select * from [TB]

declare @s nvarchar(4000)
set @s=''
Select     @s=@s+','+quotename(材料名称)+'=max(case when [材料名称]='+quotename(材料名称,'''')+' then [规格] else 0 end)'
from TB group by 材料名称
exec('select [标号]'+@s+'  from TB group by [标号]')--加多一列(学科平均分用avg([Score]))

/*
标号     废石                                      粉煤灰                                     矿粉                                      砂                                       水                                       水泥                                      碎石                                      外加剂
------ --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
C20    8154.0                                  0.0                                     0.0                                     10860.0                                 0.0                                     0.0                                     3727.0                                  26.5
C30    1999.0                                  238.0                                   251.0                                   0.0                                     269.0                                   706.0                                   0.0                                     0.0

(2 行受影响)
*/

[Mr_Nice]

参考这个总结帖http://bbs.csdn.net/topics/240002706

[lijianqiang]

如果消耗表中加入 日期字段 现要求根据时间范围查询材料消耗呢？ 标号 任务单号 合同号 盘次 材料名称 规格 用量 日期 比如查找 日期在 2013-01-01 至 2013-01-31时间范围内每种标号的记录

[xingqi_2002]

Select * From ( Select 编号, 材料名称, 用量 From 表名 ) as t pivot ( sum(用量) for 材料名称 in ([废石], [水泥], [粉煤灰], [矿粉], [水], [外加剂], [砂], [碎石]) ) as a;

[xingqi_2002]

如果列数不定，需要自己先拼列名，然后再使用Exec或sp_executesql执行

[坚_持]

if object_id('A','u') is not null drop table A go create table A ( 标号 nvarchar(6), 任务单号 nvarchar(22), 合同号 nvarchar(24), 盘次 int, 材料名称 nvarchar(6), 规格 int, 用量 int ) insert into A select 'C30','2013-000002','HT2013001007',4,'废石',1999,null union all select 'C30','2013-000002','HT2013001007',4,'水泥',706,null union all select 'C30','2013-000002','HT2013001007',4,'粉煤灰',238,null union all select 'C30','2013-000002','HT2013001007',4,'矿粉',251,null union all select 'C30','2013-000002','HT2013001007',4,'水',269,null union all select 'C20','2013-000002','HT2013001007',4,'外加剂',26.5,null union all select 'C20','2013-000002','HT2013001007',0,'砂',10860,null union all select 'C20','2013-000002','HT2013001007',0,'碎石',3727,null union all select 'C20','2013-000002','HT2013001007',0,'废石',8154,null select * from A select p.标号,isnull(p.废石,0) 废石,isnull(p.水泥,0) 水泥,isnull(p.粉煤灰,0) 粉煤灰, isnull(p.矿粉,0) 矿粉,isnull(p.水,0) 水,isnull(p.外加剂,0) 外加剂, isnull(p.砂,0) 砂,isnull(p.碎石,0) 碎石 from ( select 标号,材料名称,规格 规格 from A ) as a pivot(sum(规格) for 材料名称 in(废石 ,水泥,粉煤灰,矿粉,水,外加剂,砂,碎石))as p

[Neil2013]

本人写过的实例 http://blog.csdn.net/chenhisen/article/details/8286130

[坚_持]

if object_id('A','u') is not null drop table A go create table A ( 标号 nvarchar(6), 任务单号 nvarchar(22), 合同号 nvarchar(24), 盘次 int, 材料名称 nvarchar(6), 规格 int, 用量 int ) insert into A select 'C30','2013-000002','HT2013001007',4,'废石',1999,null union all select 'C30','2013-000002','HT2013001007',4,'水泥',706,null union all select 'C30','2013-000002','HT2013001007',4,'粉煤灰',238,null union all select 'C30','2013-000002','HT2013001007',4,'矿粉',251,null union all select 'C30','2013-000002','HT2013001007',4,'水',269,null union all select 'C20','2013-000002','HT2013001007',4,'外加剂',26.5,null union all select 'C20','2013-000002','HT2013001007',0,'砂',10860,null union all select 'C20','2013-000002','HT2013001007',0,'碎石',3727,null union all select 'C20','2013-000002','HT2013001007',0,'废石',8154,null select * from A declare @string nvarchar(4000) set @string='' select @string = @string +','+quotename(材料名称)+'=max(case when 材料名称='+quotename(材料名称,'''') +'then 规格 else 0 end)' from A group by 材料名称 execute('select 标号'+@string+' from A group by 标号')