原表:Table1
UID Type Num
----------------------------
A 001 1
A 002 3
B 002 1
C 003 2希望统计成:
UID 001 002 003 ... ... ...
-------------------------------------
A 4 0 0
B 0 1 0
C 0 0 1这种select语句怎么写?附加:类型目前是3种,以后可能会增加,这个类型也可能要动态统计,统计表的列是否也能动态生成?
UID Type Num
----------------------------
A 001 1
A 002 3
B 002 1
C 003 2希望统计成:
UID 001 002 003 ... ... ...
-------------------------------------
A 4 0 0
B 0 1 0
C 0 0 1这种select语句怎么写?附加:类型目前是3种,以后可能会增加,这个类型也可能要动态统计,统计表的列是否也能动态生成?
set @sql=''
select @sql=@sql+',sum(case type when '''+type+''' then num else 0 end) as ['+type+']'
from table1
group by type
order by typeexec('select uid'+@sql+' from table1 group by uid order by uid')-----
--ps:楼主列出的结果错误,应该是:
--UID 001 002 003 ... ... ...
-------------------------------------
--A 1 3 0
--B 0 1 0
--C 0 0 1
UID 001 002 003 ... ... ...
-------------------------------------
A 1 3 0
B 0 1 0
C 0 0 2楼上两位,是否知道这种SQL怎么写?
查找所有类别,根据结果中不同类别分别列出类别内的统计数据看下面的语句可以知道那个查询得到的字符串declare @sql varchar(8000)
set @sql=''
select @sql=@sql+',sum(case type when '''+type+''' then num else 0 end) as ['+type+']'
from table1
group by type
order by typeprint @sql--exec('select uid'+@sql+' from table1 group by uid order by uid')