请问这条SQL语句应该怎么写?
现有一个表, 结构和数据如下:
A B C
1 2 S1
1 2 S3
1 2 S2
其中A和B列为数字类型, C为字符串类型, 且C列的值只有S1,S2,S3三个, A列的值只为1, B列的值只为2, 现在我想把表的结构调整一下, 显示如下:
A B C1 C2 C3
1 2 S1 S2 S3
请问这条SQL语句应该怎么写? 谢谢.
[解决办法]
create table T(A int, B int, C varchar(10))
insert T select 1, 2, 'S1 '
union all select 1, 2, 'S3 '
union all select 1, 2, 'S2 '
select A, B,
C1=max(case when C= 'S1 ' then C end),
C2=max(case when C= 'S2 ' then C end),
C3=max(case when C= 'S3 ' then C end)
from T
group by A, B
--result
A B C1 C2 C3
----------- ----------- ---------- ---------- ----------
1 2 S1 S2 S3
(1 row(s) affected)
[解决办法]
create table T(A int, B int, C varchar(10))
insert T select 1, 2, 'S1 '
union all select 1, 2, 'S3 '
union all select 1, 2, 'S2 '
insert into t values(2,3, 's1 ')
insert into t values(2,2, 's2 ')
declare @table varchar(5000)
set @table = 'select a,b, '
select @table=@table+ '(select c from t bb where bb.c= '+QUOTENAME(c, ' ' ' ')+
' and bb.a=aa.a and bb.b=aa.b) '+QUOTENAME(c)+ ', '
from t group by c
set @table = left(@table,len(@table)-1)+ 'from t aa group by a,b '
exec (@table)
