Work_Level A B
1 01 2 1
2 02 2 1
3 03 2 1
4 04 2 1
我想把上面的表变成下面这种格式，sql语句应该怎么写
Work_Level 01 02 03 04
A 2 2 2 2
B 1 1 1 1
------解决方案--------------------
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*/