SQL 行转列
0
悬赏园豆：10
[已解决问题]
解决于 2012-04-12 15:09
这是数据表数据：
我的SQL语句这样写：
select Run_id,
case [Item_id] when 1 then [Item_data] end as Item_id1,
case [Item_id] when 2 then [Item_data] end as Item_id2,
case [Item_id] when 3 then [Item_data] end as Item_id3,
case [Item_id] when 4 then [Item_data] end as Item_id4,
case [Item_id] when 5 then [Item_data] end as Item_id5
from data
得到：
要是 Data 后面 加上 group by RUN_id 会报错
怎么修改可以得到这样的想要的结果：
最佳答案
0
给你一个参考:
1 --Sql 2000以上, min能够起作用是因为它忽略null 2 select run_id, 3 min(case item_id when 1 then item_data else null end) as item_id1, 4 min(case item_id when 2 then item_data else null end) as item_id2, 5 min(case item_id when 3 then item_data else null end) as item_id3, 6 min(case item_id when 4 then item_data else null end) as item_id4, 7 min(case item_id when 5 then item_data else null end) as item_id5 8 from data 9 group by run_id 10 --Sql 2005以上 11 SELECT run_id, 12 [1] AS item_id1, 13 [2] AS item_id2, 14 [3] AS item_id3, 15 [4] AS item_id4, 16 [5] AS item_id5 17 FROM data PIVOT 18 (min (item_data) 19 FOR data.item_id IN 20 ([1], [2], [3], [4], [5]) 21 ) AS pvt 22 ORDER BY run_id
收获园豆：10
其他回答(2)
1
select Run_id, MAX(case [Item_id] when 1 then [Item_data] end as Item_id1), MAX(case [Item_id] when 2 then [Item_data] end as Item_id2), MAX(case [Item_id] when 3 then [Item_data] end as Item_id3), MAX(case [Item_id] when 4 then [Item_data] end as Item_id4), MAX(case [Item_id] when 5 then [Item_data] end as Item_id5) from data GROUP BY Run_id
语句有点小错啊? 不过也学了一招，case 后面可以不写 else，前面都不匹配，缺省就是null啦。
@ChatinCode: 就是在網頁上手寫的，反正你知道是這個意思就行了。
0
select top 1
(select top 1 Item_data from data where Item_id=1) as item_id1,
(select top 1 Item_data from data where Item_id=2) as item_id2,
(select top 1 Item_data from data where Item_id=3) as item_id3,
(select top 1 Item_data from data where Item_id=4) as item_id4,
(select top 1 Item_data from data where Item_id=5) as item_id5
from data
呵呵，应该就可以满足你想要的结果
