sql行列转换解决方案

sql行列转换
Work_LevelAB
10121
20221
30321
40421

我想把上面的表变成下面这种格式，sql语句应该怎么写

  Work_Level 01 020304
A2222
B1111

[解决办法]
select *
from (
select 'A' as item,work_level,A as V
from tablename 
uinon all
select 'B' as item,work_level,B as V
from tablename) as TB1
pivot(max(work_level) for V in ([01],[02],[03],[04])) as TB2
[解决办法]
sql2000

SQL code

--> 测试数据：#tbIF OBJECT_ID('TEMPDB.DBO.#tb') IS NOT NULL DROP TABLE #tbGO CREATE TABLE #tb([id] INT,[Work_Level] VARCHAR(10),[A] INT,[B] INT)INSERT #tbSELECT 1,'01',2,1 UNION ALLSELECT 2,'02',2,1 UNION ALLSELECT 3,'03',2,1 UNION ALLSELECT 4,'04',2,1--------------开始查询--------------------------select col1,max(case when work_level='01' then col3 else 0 end) as [01],max(case when work_level='02' then col3 else 0 end) as [02],max(case when work_level='03' then col3 else 0 end) as [03],max(case when work_level='04' then col3 else 0 end) as [04]from(select 'A' as col1,work_level,A as col3 from #tb  union allselect 'B' as col1,work_level,B as col3 from #tb) tgroup by col1/*col1    01    02    03    04A    2    2    2    2B    1    1    1    1*/