SQL中行转列的方法【简单详细】

SQL中行转列的方法

Sql中的的PIVOT:

最近自己在进行一个功能的查询时,发现要拿取查询一张表某一列的数据转成行输出为一张临时表引用

但是:突然发现,怎么用SQL实现列转行;发现PIVOT真好适合(下饭~学习一波!)(参考好几篇…)

1.总纲速览

作用:

1. 将表达式的某一列中的唯一值转换为输出中的多个列来旋转表值表达式,
2. 且在必要时最终输出中所需的任何其余列值执行聚合.

大白话:

就是实现列转行

2.具体代码实现

2.1首先创建测试表

--创建测试表
create table DailyIncome(VendorId nvarchar(50), IncomeDay nvarchar(50), IncomeAmount int,FacName nvarchar(50))
--插入数据(根据需要自己添加测试)
insert into  DailyIncome(VendorId,IncomeDay,IncomeAmount,FacName)
select  '1002','MON',15000,'MES_1' UNION ALL
select  '1001','TUE',16000,'MES_2' UNION ALL
select  '1001','THU',17000,'MES_3' UNION ALL
select  '1001','FRI',18000,'MES_4' UNION ALL
select  '1001','SAT',19000,'MES_5' UNION ALL
select  '1001','SUN',25000,'MES_6' UNION ALL
select  '1002','TUE',16000,'MES_2' UNION ALL
select  '1003','THU',17000,'MES_3' UNION ALL
select  '1004','FRI',18000,'MES_4' UNION ALL
select  '1005','SAT',19000,'MES_5' UNION ALL
select  '1006','SUN',25000,'MES_6' 

使用CASE WHEN实现

这代码没啥解释的…

select VendorId ,
            sum(case when IncomeDay='MoN' then IncomeAmount else 0 end) as MON,
            sum(case when IncomeDay='TUE' then IncomeAmount else 0 end) as TUE,
            sum(case when IncomeDay='WED' then IncomeAmount else 0 end) as WED,
            sum(case when IncomeDay='THU' then IncomeAmount else 0 end) as THU,
            sum(case when IncomeDay='FRI' then IncomeAmount else 0 end) as FRI,
            sum(case when IncomeDay='SAT' then IncomeAmount else 0 end) as SAT,
            sum(case when IncomeDay='SUN' then IncomeAmount else 0 end) as SUN
from DailyIncome group by VendorId

2.2使用pivot函数实现行转列

这里我们采用经典三部曲法

1. 首先明白数据源,我们这里数据源=DailyIncome
2. 新列值:确定哪一列的值作为新列的值
3. 确定新列的要求的那些值

select * from 
	(select VendorId,IncomeAmount,IncomeDay from DailyIncome) as #temp1  --第一步数据源
	pivot
	(
		sum (IncomeAmount) ----第三步
		for IncomeDay in ([MON],[TUE],[WED],[THU],[FRI],[SAT],[SUN]) ---第二步
	) as   AvgIncomePerDay

注意:使用pivot函数,会把选中的其他值列作为group by的分组条件

执行结果:

3.补充

行转列

3.1UNION ALL

select Id,StuName,Course,Score,ROW_NUMBER()over(order by Id asc) as RowIndex from
(
        select Id,StuName,Course='英语',Score=YingYu from Scores
        union all
        select Id,StuName,Course='语文',Score=YuWen from Scores
        union all
        select Id,StuName,Course='化学',Score=HuaXue from Scores
) as #temp1
where StuName='jay'

3.2UNPIVOT

select * from Scores -- 数据源
UNPIVOT
(
	Score for Subject in ([YingYu],[YuWen],[HuaXue])
) as StuScores

OK!!!致辞结束