请问这条SQL语句应该怎么写?

[s_hluo]

现有一个表, 结构和数据如下:

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语句应该怎么写? 谢谢.

[marco08]

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

[marco08]

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)

[s_hluo]

如果C列的值是只有三个, 但是不确定是什么值的话怎么办?
比如C列的值可能是: H,J,K 也可能是U,M ,L ...
请问该怎么写?

[w75251455]

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)

[w75251455]

问这种题~应该是40分以上的!

[zsl5305256]

那也要有个规律;
如c1,c2,c3分别要与那些值对应!

[s_hluo]

c1, c2, c3的值相当于传参数进去.

[s_hluo]

to: w75251455(砍破)
我看懂了你的意思, :), 谢谢.

不过现在我想用一条SQL语句实现, 不想动态执行SQL语句.

不知道能不能实现?

[w75251455]

to:不知道能不能实现?
反正我不会....

[marco08]

create table T(A int, B int, C varchar(10))
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)

[w75251455]

TO:marco08(天道酬勤) ( )
别人都说了里面不只有三个~~还不一定有几个~~你搞什么飞机啊

[s_hluo]

To: marco08(天道酬勤)

谢谢你耐心的解答, :)

如果C列有3至10个值的话, 写起来是不是会比较麻烦?

[believe209]

mark!
收藏!

[s_hluo]

谢谢各位. 结贴.