表结构、create table DailyIncome(VendorId nvarchar(50), IncomeDay nvarchar(50), IncomeAmount int,FacName nvarchar(50))
1、使用select sum case when then end as 方式实现行转列
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、使用pivot函数实现行转列
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
第一步：肯定是要明白数据源了，这里是DailyIncome
第二步：要明白要想让哪一列的值做新的列名字
第三步：要明白对于这新的列要求那些值
注：使用pivot函数，会把没选中的其他列作为group by的分组条件
3、使用变量实现行转列
DECLARE @sql11 VARCHAR(max)
SET @sql11='' --初始化变量@sql
SELECT @sql11=@sql11+',['+ IncomeDay +']' FROM DailyIncome
GROUP BY IncomeDay --变量多值赋值
SET @sql11=stuff(@sql11,1,1,'')--去掉首个','
--print @sql11
SET @sql11='select VendorId,'+@sql11+' from DailyIncome
pivot (max (IncomeAmount) for IncomeDay in ('+@sql11++')) as MaxIncomePerDay'
exec(@sql11)
二、unpivot 列转行
表结构：Id,StuName,YingYu,YuWen,HuaXue
1、使用union all和临时表实现列转行，如果用union会去重。
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'
2、使用unpivot实现列转行(高级写法，推荐)
select * from Scores -- 数据源
unpivot
(
Score for Subject in ([YingYu],[YuWen],[HuaXue])
) as StuScores
结果：Id,StuName,Score,Subject
1 jay 20 YingYu
1 jay 60 YuWen
1 jay 80 HuaXue
2 jack 70 YingYu
2 jack 10 YuWen
2 jack 66 HuaXue
解释：Score for Subject in ([YingYu],[YuWen],[HuaXue]) 中 Score 和 Subject 是自定义的新列名，Score 的值是 in([YingYu],[YuWen],[HuaXue])中原数据列对应的值，Subject的值对应的是列名。
来源：https://blog.csdn.net/qingwenc/article/details/108382885