sql server 列转行

kyupis 2017-08-24 09:47:01

id name Q1 Q2 Q3 Q4
----------- -------------------- ----------- ----------- ----------- -----------
2 b 3000 3500 4200 5500

转成

id 2
name b
Q1 3000
Q2 3500
Q3 4200
Q4 5500

这种格式

...全文

344 2 打赏收藏转发到动态举报

写回复

用AI写文章

2 条回复

切换为时间正序

请发表友善的回复…

发表回复

kyupis 2017-08-30

打赏
举报

引用 1 楼 ch21st 的回复:

两种方法实现的例子：


;with tb(id,name,Q1,Q2,Q3,Q4)AS
(
  select 2,'b',3000,3500,4200,5500
)
---1 UNPIVOT
select col,val from (
  select convert(varchar(100),id) as id,convert(varchar(100),name) as name,convert(varchar(100),Q1) as Q1,convert(varchar(100),Q2) as Q2,convert(varchar(100),Q3) as Q3,convert(varchar(100),Q4) as Q4
  from tb
) as t
UNPIVOT (val FOR col IN (id,name,Q1,Q2,Q3,Q4)) unpvt

/*  2 cross apply
select c.* from tb
cross apply(values('id',ltrim(id)),('name',name),('Q1',ltrim(Q1)),('Q2',ltrim(Q2)),('Q3',ltrim(Q3)),('Q4',ltrim(Q4)))c(col,val)
*/


+------+------+
| col  | val  |
+------+------+
| id   | 2    |
| name | b    |
| Q1   | 3000 |
| Q2   | 3500 |
| Q3   | 4200 |
| Q4   | 5500 |
+------+------+

thank you！

道素 2017-08-24

打赏
举报

两种方法实现的例子：


;with tb(id,name,Q1,Q2,Q3,Q4)AS
(
  select 2,'b',3000,3500,4200,5500
)
---1 UNPIVOT
select col,val from (
  select convert(varchar(100),id) as id,convert(varchar(100),name) as name,convert(varchar(100),Q1) as Q1,convert(varchar(100),Q2) as Q2,convert(varchar(100),Q3) as Q3,convert(varchar(100),Q4) as Q4
  from tb
) as t
UNPIVOT (val FOR col IN (id,name,Q1,Q2,Q3,Q4)) unpvt

/*  2 cross apply
select c.* from tb
cross apply(values('id',ltrim(id)),('name',name),('Q1',ltrim(Q1)),('Q2',ltrim(Q2)),('Q3',ltrim(Q3)),('Q4',ltrim(Q4)))c(col,val)
*/


+------+------+
| col  | val  |
+------+------+
| id   | 2    |
| name | b    |
| Q1   | 3000 |
| Q2   | 3500 |
| Q3   | 4200 |
| Q4   | 5500 |
+------+------+