表Create table ttt
(ID int not null,
SID int ,
A varchar(10))
INsert into ttt
Select 1,1,'aa'
Union
Select 1,2,'bb'
union
Select 1,3,'cc'
Union
Select 2,1,'a'
Union
Select 2,2,'B'
Select ID,Case when sid=1 then A else Null End as sid1,
Case When Sid=2 then A else Null End as sid2,
Case When Sid=3 then A Else Null End as Sid3
From ttt group by id,A,Sid
我想要得到的结果是 ID,Sid1,Sid2,Sid3
1, aa, bb, cc
2, a, B
---进行到上面一步查询不会了 谢谢
(ID int not null,
SID int ,
A varchar(10))
INsert into ttt
Select 1,1,'aa'
Union
Select 1,2,'bb'
union
Select 1,3,'cc'
Union
Select 2,1,'a'
Union
Select 2,2,'B'
Select ID,Case when sid=1 then A else Null End as sid1,
Case When Sid=2 then A else Null End as sid2,
Case When Sid=3 then A Else Null End as Sid3
From ttt group by id,A,Sid
我想要得到的结果是 ID,Sid1,Sid2,Sid3
1, aa, bb, cc
2, a, B
---进行到上面一步查询不会了 谢谢
Select ID,
max(Case when SID=1 then A else Null End) SID1 ,
max(Case when SID=2 then A else Null End) SID2 ,
max(Case when SID=3 then A else Null End) SID3
From ttt group by ID
Create table ttt
(ID int not null,
[SID] int ,
A varchar(10))
INsert into ttt
Select 1,1,'aa'
Union
Select 1,2,'bb'
union
Select 1,3,'cc'
Union
Select 2,1,'a'
Union
Select 2,2,'B'--[SID]字段值较多时推荐使用动态转换
declare @str varchar(1000)
set @str=''
select
@str=@str+',['+ltrim([SID])+']=max(case when [SID]='
+LTRIM([SID])+' then A else '''' end)'
from
ttt
group by
[SID]
exec('select ID'+@str+' from ttt group by ID')
/*
ID 1 2 3
-----------------
1 aa bb cc
2 a B
*/
Create table ttt
(ID int not null,
[SID] int ,
A varchar(10))
INsert into ttt
Select 1,1,'aa'
Union
Select 1,2,'bb'
union
Select 1,3,'cc'
Union
Select 2,1,'a'
Union
Select 2,2,'B'--[SID]字段值较多时推荐使用动态转换
declare @str varchar(1000)
set @str=''
select
@str=@str+',[SID'+ltrim([SID])+']=max(case when [SID]='
+LTRIM([SID])+' then A else '''' end)'
from
ttt
group by
[SID]
exec('select ID'+@str+' from ttt group by ID')
/*
ID SID1 SID2 SID3
------------------------------
1 aa bb cc
2 a B
*/
Create table ttt
(ID int not null,
SID int ,
A varchar(10))INsert into ttt
Select 1,1,'aa'
Union
Select 1,2,'bb'
union
Select 1,3,'cc'
Union
Select 2,1,'a'
Union
Select 2,2,'B'
select ID,
isnull([1],'') Sid1,
isnull([2],'') Sid2,
isnull([3],'') Sid3
from ttt a
pivot(max(A) for SID in([1],[2],[3])) p/*
ID Sid1 Sid2 Sid3
----------- ---------- ---------- ----------
1 aa bb cc
2 a B (2 row(s) affected)
*/