现有一个表, 结构和数据如下: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语句应该怎么写? 谢谢.
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语句应该怎么写? 谢谢.
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
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)
比如C列的值可能是: H,J,K 也可能是U,M ,L ...
请问该怎么写?
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)
如c1,c2,c3分别要与那些值对应!
我看懂了你的意思, :), 谢谢.不过现在我想用一条SQL语句实现, 不想动态执行SQL语句.不知道能不能实现?
insert T select 1, 2, 'H'
union all select 1, 2, 'J'
union all select 1, 2, 'K'select A, B,
C1=(select min(C) from T where A=tmp.A and B=tmp.B),
C2=(select top 1 C from T where A=tmp.A and B=tmp.B and C not in(
select min(C) from T where A=tmp.A and B=tmp.B union all select max(C) from T where A=tmp.A and B=tmp.B
)),
C3=(select max(C) from T where A=tmp.A and B=tmp.B)
from T as tmp
group by A, B--result
A B C1 C2 C3
----------- ----------- ---------- ---------- ----------
1 2 H J K(1 row(s) affected)
别人都说了里面不只有三个~~还不一定有几个~~你搞什么飞机啊