SqlServer 查询的问题

Question

sql server 查询怎么将下列值

按照cId的值分成3列
例如：

cId1	cId2	cId0
A203D53A-A692-46AF-9C3D-0000453FC531	9EE29150-DFF6-4FB2-B292-000A3657747B	57C125E1-9A0D-4AF8-B0AD-00122388F4C0
47E1972B-53B0-4D43-8C00-002AB9661CCE	4FB1E188-009C-4150-AD93-002DE24EAB9E	B2EBA976-3E11-4556-82E0-004DA78734B4

其中cId1列为cId=1的列 依次类推。
相当于
怎么把

select Id cId0 from table_name where cId=0;
select Id cId1 from table_name where cId=1;
select Id cId2 from table_name where cId=2;

三列值 并起来

Answer 1

利用行号

select t0.Id cId0,t1.Id cId1,t2.Id cId2 from 
(select Id,ROW_NUMBER() over(order by Id)) rNum from table_name where cId=0) t0
left join
(select Id,ROW_NUMBER() over(order by Id)) rNum from table_name where cId=1) t1 on t0.rNum=t1.rNum
left join
(select Id,ROW_NUMBER() over(order by Id)) rNum from table_name where cId=2) t2 on t1.rNum=t2.rNum

Answer 2

select max(case cId when 0 then id else '' end) cid0,

   max(case cId when 1 then id else '' end) cid1,
   max(case cId when 2 then id else '' end) cid2
   from your_table 

你试试

Answer 3

也可以用转置操作，但是你的数据要稍微改下，否则如果cid同样的值都存在多行没法确定哪三对变成一行
下面我造的例子是按循序三个算一组，到时变成一行

WITH a(g,id,cid) AS (
    SELECT 1,'A203D53A-A692-46AF-9C3D-0000453FC531',1 UNION
    SELECT 1,'9EE29150-DFF6-4FB2-B292-000A3657747B',2 UNION
    SELECT 1,'57C125E1-9A0D-4AF8-B0AD-00122388F4C0',0 UNION
    SELECT 2,'47E1972B-53B0-4D43-8C00-002AB9661CCE',1 UNION
    SELECT 2,'4FB1E188-009C-4150-AD93-002DE24EAB9E',2 UNION
    SELECT 2,'B2EBA976-3E11-4556-82E0-004DA78734B4',0 
)
--SELECT * FROM a
SELECT g, [1] AS col1,[2] AS cl2,[0]  AS col0 FROM a
PIVOT(max(id) FOR cid IN([1],[2],[0]))b

结果
g col1 cl2 col0
1 A203D53A-A692-46AF-9C3D-0000453FC531 9EE29150-DFF6-4FB2-B292-000A3657747B 57C125E1-9A0D-4AF8-B0AD-00122388F4C0
2 47E1972B-53B0-4D43-8C00-002AB9661CCE 4FB1E188-009C-4150-AD93-002DE24EAB9E B2EBA976-3E11-4556-82E0-004DA78734B4