SQL语句对查询结果进行行转列
测试数据:
code price
'MCU0-3' 15
'MCU0-3' 20
'MCU0-3' 22
'MCU3-15' 17
'MCU3-15' 16
'MCU3-15' -10
'MCU3-15' 50
'MCU3-27' 99
'MCU3-27' 96
'MCU3-27' 54
'MCU3-27' 14
'MCU3-27' 46
'MCU3-27' 86
结果:
MCU0-3 MCU3-15 MCU3-27
15 17 99
20 16 96
22 -10 54
null 50 14
null null 46
null null 86
SQL语句:
1SELECT ID = IDENTITY(INT, 1, 1), price into #T1 from [table] where code = 'MCU0-3'
2 SELECT ID = IDENTITY(INT, 1, 1), price into #T2 from [table] where code = 'MCU3-15'
3
4 SELECT ID = IDENTITY(INT, 1, 1), price into #T3 from [table] where code = 'MCU3-27'
5
6 select t1.price as MCU0-3, t2.price as MCU3-15,t3.price as MCU3-27 from #T1 t1 FULL OUTER JOIN #T2 t2 on t1.id = t2.id FULL OUTER JOIN #T3 t3 on t2.id = t3.id
7
2 SELECT ID = IDENTITY(INT, 1, 1), price into #T2 from [table] where code = 'MCU3-15'
3
4 SELECT ID = IDENTITY(INT, 1, 1), price into #T3 from [table] where code = 'MCU3-27'
5
6 select t1.price as MCU0-3, t2.price as MCU3-15,t3.price as MCU3-27 from #T1 t1 FULL OUTER JOIN #T2 t2 on t1.id = t2.id FULL OUTER JOIN #T3 t3 on t2.id = t3.id
7