行转列

有两个行转列的例子跟大家分享：

一、这个是曾经帮同事解决的一个例子：

--创建表：
create table HC_Technical
(id int,t_userid int,t_level int,t_name varchar(20))
insert into HC_Technical values(8,184,1,'100200300')
insert into HC_Technical values(9,184,2,'119119119')
insert into HC_Technical values(10,184,3,'445445445')
insert into HC_Technical values(6,290,4,'Hello11')
insert into HC_Technical values(1,323,6,'TestTest')
insert into HC_Technical values(2,323,5,'Hello')
insert into HC_Technical values(4,358,8,'ddd')
insert into HC_Technical values(5,358,10,'dd')
insert into HC_Technical values(7,359,9,'12e')

查询新建的表，数据如下：

要求实现的效果：

这个其实也就是要根据t_userid分组，基于t_userid,将其他字段值转换为列：

下面两种方法很相似：

方法1：

with HC_1 as
(
select min(id) id, t_userid from HC_Technical group by t_userid
),
HC_2 as
(
select *,row_number() over(partition by t_userid order by id) rowid from HC_Technical
)
select hc1.id
,hc1.t_userid
,(select t_level from HC_2 hc2 where hc1.t_userid=hc2.t_userid and hc2.rowid=1) t_level
,(select t_level from HC_2 hc2 where hc1.t_userid=hc2.t_userid and hc2.rowid=2) t_level2
,(select t_level from HC_2 hc2 where hc1.t_userid=hc2.t_userid and hc2.rowid=3) t_level3
,(select t_name from HC_2 hc2 where hc1.t_userid=hc2.t_userid and hc2.rowid=1) t_name
,(select t_name from HC_2 hc2 where hc1.t_userid=hc2.t_userid and hc2.rowid=2) t_name2
,(select t_name from HC_2 hc2 where hc1.t_userid=hc2.t_userid and hc2.rowid=3) t_name3
from HC_1 hc1

 

方法2：

select
MIN(hc.id) id
,hc.t_userid
,MIN(case rowid when 1 then t_level end) t_level
,MIN(case rowid when 2 then t_level end) t_level2
,MIN(case rowid when 3 then t_level end) t_level3
,MIN(case rowid when 1 then t_name end) t_name
,MIN(case rowid when 2 then t_name end) t_name2
,MIN(case rowid when 3 then t_name end) t_name3
from (select *,row_number() over(partition by t_userid order by id) rowid from HC_Technical) hc group by t_userid

 

二、这是自己做过的一个项目的例子，比起上面的应该简单一些

创建一个临时表：

create table #a
(orderDate date,orderName varchar(20),orderNum int)
insert into #a values('2014-04-01','百家讲坛',6)
insert into #a values('2014-04-01','奇闻故事',8)
insert into #a values('2014-04-01','文化中国',null)
insert into #a values('2014-04-02','百家讲坛',7)
insert into #a values('2014-04-02','奇闻故事',0)
insert into #a values('2014-04-02','文化中国',12)
insert into #a values('2014-04-03','百家讲坛',45)
insert into #a values('2014-04-03','奇闻故事',8)
insert into #a values('2014-04-03','文化中国',444)
insert into #a values('2014-04-04','百家讲坛',67)
insert into #a values('2014-04-04','奇闻故事',232)
insert into #a values('2014-04-04','文化中国',8798)
insert into #a values('2014-04-05','百家讲坛',34)
insert into #a values('2014-04-05','奇闻故事',78)
insert into #a values('2014-04-05','文化中国',343)
insert into #a values('2014-04-06','文化中国',61)
insert into #a values('2014-04-06','文化中国',223)

数据如下：

 要达到下面的效果：

这里要根据时间，把每个orderName的值作为一列显示其orderNum之和：

方法：

declare @sql0 varchar(8000)
select @sql0 = isnull(@sql0 + '],[' , '') + orderName from #a group by orderName
set @sql0 = '[' + @sql0 + ']'
exec('select * from (select convert(varchar(10),orderDate,120) 时间,orderName,orderNum from #a ) a pivot (sum(orderNum) for orderName in ('+@sql0+')) b')