日期:2014-05-17  浏览次数:20552 次

这个列转行该如何实现?

SQL code

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





我想要得到的结果是 
SQL code

ID,Sid1,Sid2,Sid3
1, aa,   bb,  cc
2, a,    B
---进行到上面一步查询不会了 


谢谢

------解决方案--------------------
SQL code

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

------解决方案--------------------
SQL code

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    
*/

------解决方案--------------------
SQL code

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        
*/

------解决方案--------------------
SQL code

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)
*/