首页新闻找找看学习计划

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 会报错

 

怎么修改可以得到这样的想要的结果:

呼嘎嘎的主页 呼嘎嘎 | 初学一级 | 园豆:65
提问于:2012-04-12 13:55
< >
分享
最佳答案
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
ChatinCode | 老鸟四级 |园豆:2272 | 2012-04-12 15:02
其他回答(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
写代码的小2B | 园豆:4346 (老鸟四级) | 2012-04-12 14:57

语句有点小错啊? 不过也学了一招,case 后面可以不写 else,前面都不匹配,缺省就是null啦。

支持(0) 反对(0) ChatinCode | 园豆:2272 (老鸟四级) | 2012-04-12 15:12

@ChatinCode: 就是在網頁上手寫的,反正你知道是這個意思就行了。

支持(0) 反对(0) 写代码的小2B | 园豆:4346 (老鸟四级) | 2012-04-12 18:36
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

呵呵,应该就可以满足你想要的结果

xingyuan13 | 园豆:202 (菜鸟二级) | 2012-04-12 14:59
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册