mssql 行列转换

xzfyes

于 2022-11-25 21:35:30 发布

阅读量595

点赞数

文章标签： sqlserver sql 数据库

本文链接：https://blog.csdn.net/xzfyes/article/details/128044905

版权

先建立表

If Exists (Select * From sysobjects Where
id = OBJECT_ID('Sales') and OBJECTPROPERTY(id, 'IsUserTable') = 1)
    Begin
        Drop Table Sales
    End

Create Table Sales(
Name nvarchar(10),
Product nvarchar(12),
Quantity int)

Insert Into Sales(Name,Product,Quantity)
Values('Leo','Apple',300),
('Leo','Orange',23),
('Amy','Apple',432),
('Amy','Banana',45),
('Leo','Banana',74),
('Tomy','Apple',57),
('Tomy','Orange',92),
('Tim','Apple',76),
('Tim','Banana',45),
('Tim','Orange',159),
('Amy','Orange',610),
('Alice','Apple',245),
('Alice','Banana',342),
('Alice','Orange',138)
Go

做一个按product的行列转换

select * from Sales
Declare @sql varchar(8000)
Set @sql=(Select DISTINCT ','+ Product From Sales FOR XML PATH(''))
select @sql
Set @sql=STUFF(@sql,1,1,'')
select @sql
Set @sql='Select * From Sales
PIVOT
(
    SUM(Quantity) For Product in('+@sql+')
) as pvt'
exec(@sql)

再来加个合计

declare @s nvarchar(4000)
Select @s=isnull(@s+',','')+quotename([Product]) from Sales group by[Product] 
--isnull(@s+',','') 去掉字符串@s中第一个逗号
select @s
exec('select [Name],'+@s+',[总成绩] from (select *,[总成绩]=sum([Quantity])over(partition by [Name]) from Sales) a
pivot (max([Quantity]) for [Product] in('+@s+'))b ')