交叉create table #(A varchar(20),B varchar(20),C varchar(20))
insert into #
select 'x','01','aaa' union
select 'x','02','bbb' union
select 'x','03','ccc' union
select 'y','01','ddd' union
select 'y','03','eee' union
select 'z','04','fff'
go--处理语句
declare
@sql varchar(8000),
@sq varchar(8000)select @sql=''
select @sql=@sql+',['+B+']=max(case B when '''+B+''' then C end)'
from #
group by Bset @sq='select A'+@sql+' from # group by A'print @sq
exec(@sq)
--删除测试表
drop table #
go
select A,[01]=max(case B when '01' then C end),[02]=max(case B when '02' then C end),[03]=max(case B when '03' then C end),[04]=max(case B when '04' then C end) from # group by A
insert into #
select 'x','01','aaa' union
select 'x','02','bbb' union
select 'x','03','ccc' union
select 'y','01','ddd' union
select 'y','03','eee' union
select 'z','04','fff'
go--处理语句
declare
@sql varchar(8000),
@sq varchar(8000)select @sql=''
select @sql=@sql+',['+B+']=max(case B when '''+B+''' then C end)'
from #
group by Bset @sq='select A'+@sql+' from # group by A'print @sq
exec(@sq)
--删除测试表
drop table #
go
select A,[01]=max(case B when '01' then C end),[02]=max(case B when '02' then C end),[03]=max(case B when '03' then C end),[04]=max(case B when '04' then C end) from # group by A
(id Int,
shuyu Varchar(10),
pinmin Int,
zhuangtai Varchar(10))
Insert table1 Select 1, 'A', 11, '@'
Union All Select 2, 'B', 11, '@'
Union All Select 3, 'A', 22, '#'
Union All Select 4, 'C', 12, '$'
Union All Select 5, 'C', 44, '*'
Union All Select 6, 'B', 11, '@'
GO
Declare @S Varchar(8000)
Select @S = 'Select pinmin, shuyu'
Select @S = @S + ' , SUM(Case zhuangtai When ''' + zhuangtai + ''' Then 1 Else 0 End) As ' + QUOTENAME(zhuangtai)
From table1 Group By zhuangtai
Select @S = @S + ' , Count(*) As zongshu From table1 Group By pinmin, shuyu Order By pinmin, shuyu'
EXEC(@S)
GO
Drop Table table1
--Result
/*
pinmin shuyu # $ * @ zongshu
11 A 0 0 0 1 1
11 B 0 0 0 2 2
12 C 0 1 0 0 1
22 A 1 0 0 0 1
44 C 0 0 1 0 1
*/
(id Int,
shuyu Varchar(10),
pinmin Int,
zhuangtai Varchar(10))
Insert table1 Select 1, 'A', 11, '@'
Union All Select 2, 'B', 11, '@'
Union All Select 3, 'A', 22, '#'
Union All Select 4, 'C', 12, '$'
Union All Select 5, 'C', 44, '*'
Union All Select 6, 'B', 11, '@'
GOselect pinmin,shuyu,max([@])as [@],max([#]) as [#] ,max([$]) as [$] ,max([*]) as [*],zongshu=count(1)
from
(
select pinmin,shuyu,[@],[#],[$],[*] from table1
pivot
(count(zhuangtai)
for zhuangtai in([@],[#],[$],[*])
) as pt
) tb
group by pinmin,shuyu
order by pinmindrop table table1/*
pinmin shuyu @ # $ * zongshu
----------- ---------- ----------- ----------- ----------- ----------- -----------
11 A 1 0 0 0 1
11 B 1 0 0 0 2
12 C 0 0 1 0 1
22 A 0 1 0 0 1
44 C 0 0 0 1 1(5 行受影响)*/