题目很拗口大家见谅需求是类似这个样子的:ID ClassID SortID Value
1 1 1 111
1 1 2 122
1 1 3 133
1 2 1 144
1 2 2 155
2 1 1 211
2 1 2 222
2 1 3 233
2 2 1 244
2 2 2 255变成
ID t11 t12 t13 t21 t22
1 111 122 133 144 155
2 211 222 233 244 255问题不难 但是实在没有好的思路 希望好心人能帮我解答一下 多谢大家了
1 1 1 111
1 1 2 122
1 1 3 133
1 2 1 144
1 2 2 155
2 1 1 211
2 1 2 222
2 1 3 233
2 2 1 244
2 2 2 255变成
ID t11 t12 t13 t21 t22
1 111 122 133 144 155
2 211 222 233 244 255问题不难 但是实在没有好的思路 希望好心人能帮我解答一下 多谢大家了
;with cte as(
select *,rid=ROW_NUMBER() over (PARTITION by id order by value) from tb
)
select id,
max(case rid when 1 then value else 0 end) [t11] ,
max(case rid when 2 then value else 0 end) [t12] ,
max(case rid when 3 then value else 0 end) [t13] ,
max(case rid when 4 then value else 0 end) [t21] ,
max(case rid when 5 then value else 0 end) [t22]
from cte
group by id/*
id t11 t12 t13 t21 t22
----------- ----------- ----------- ----------- ----------- -----------
1 111 122 133 144 155
2 211 222 233 244 255(2 行受影响)
--如果需要动态,如下:
select *,rid=ROW_NUMBER() over (PARTITION by id order by value) into #temptb from tbdeclare @sql varchar(8000)
set @sql = 'select id'
select @sql = @sql + ',max(case rid when ' + ltrim(rid) + ' then value end) [' + ltrim(rid) + ']'
from (select distinct rid from #temptb) as t
set @sql = @sql + ' from #temptb group by id'
--print @sql
exec(@sql)
id,
max(case id when 1 then value else 0 end) [t11] ,
max(case id when 2 then value else 0 end) [t12] ,
max(case id when 3 then value else 0 end) [t13] ,
max(case id when 4 then value else 0 end) [t21] ,
max(case id when 5 then value else 0 end) [t22]
from (select *,id=ROW_NUMBER() over (PARTITION by id order by value) from tb)t
group by
id
id,
max(case px when 1 then value else 0 end) [t11] ,
max(case px when 2 then value else 0 end) [t12] ,
max(case px when 3 then value else 0 end) [t13] ,
max(case px when 4 then value else 0 end) [t21] ,
max(case px when 5 then value else 0 end) [t22]
from (select *,px=ROW_NUMBER() over (PARTITION by id order by value) from tb)t
group by
id
CREATE TABLE TEST
(
ID INT,
ClassID INT,
SortID INT,
Value INT
)INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(1,1,1,111)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(1,1,2,122)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(1,1,3,133)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(1,2,1,144)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(1,2,2,155)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(2,1,1,211)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(2,1,2,222)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(2,1,3,333)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(2,2,1,244)
INSERT INTO TEST(ID,ClassID,SortID,Value) VALUES(2,2,2,255) SELECT * FROM
(SELECT ID,'t'+CAST(ClassID AS NVARCHAR)+CAST(SortID AS NVARCHAR) AS PivotColumn,Value FROM TEST) AS T_TEST
PIVOT
(
SUM(Value) FOR PivotColumn IN (t11,t12,t13,t21,t22)
) AS T_TEMPDROP TABLE TEST
/*执行结果*/
ID t11 t12 t13 t21 t22
1 111 122 133 144 155
2 211 222 333 244 255