前边介绍了静态转换和PIVOT、UNPIVOT转换的方式,这次介绍一下动态拼接转换的形式,有时候我们的数据不是固定的,可能会有多条,所以需要我们动态进行拼接实现,行转列测试数据:
--测试数据
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([Name] nvarchar(22),[Subject] nvarchar(22),[Score] int)
Insert #T
select N'李四',N'语文',60 union all
select N'李四',N'数学',70 union all
select N'李四',N'英语',80 union all
select N'张三',N'语文',90 union all
select N'张三',N'数学',80 union all
select N'张三',N'英语',70
Go
--测试数据结束
动态写法(加上了总分和平均分):
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select Name'
SELECT @sql = @sql + ',max(case Subject when ''' + Subject
+ ''' then Score else 0 end)[' + Subject + ']'
FROM ( SELECT DISTINCT
Subject
FROM #T
) a
SET @sql = @sql
+ ',sum(Score) 总分,cast(avg(Score*1.0) as decimal(18,2)) 平均分 from #T group by Name'
EXEC(@sql)
动态的也可以使用pivot:
DECLARE @sql VARCHAR(MAX)
SELECT @sql=isnull(@sql+',','')+Subject FROM #T GROUP BY Subject
SET @sql='select m.* , n.总分, n.平均分 from
(select * from (select * from #T) a pivot (max(Score) for Subject in ('+@sql+')) b) m ,
(select Name,sum(Score)总分, cast(avg(Score*1.0) as decimal(18,2))平均分 from #T group by Name) n
where m.Name= n.Name'
exec(@sql)
结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/6adecc21b0524ff95f29cc078ed7692d.png)
列转行的测试数据:
--测试数据
if not object_id(N'T') is null
drop table T
Go
Create table T([姓名] nvarchar(22),[语文] int,[数学] int,[英语] int)
Insert T
select N'李四',60,70,80 union all
select N'张三',90,80,70
Go
--测试数据结束
动态写法:
DECLARE @sql VARCHAR(8000)
SELECT @sql=isnull(@sql+' union all ','')+' select 姓名, [课程]='
+quotename(Name,'''')+' , [分数] = '+quotename(Name)+' from T'
FROM syscolumns
WHERE Name!='姓名' AND ID=object_id('T')--表名tb,不包含列名为姓名的其他列
ORDER BY colid
EXEC(@sql+' order by 姓名')
同样的动态写法也可以使用unpivot:
DECLARE @sql VARCHAR(8000)
SELECT @sql=isnull(@sql+',','')+quotename(Name)
FROM syscolumns
WHERE ID=object_id('T')AND Name NOT IN('姓名')
ORDER BY Colid
SET @sql='select 姓名,[课程],[分数] from T unpivot ([分数] for [课程] in('+@sql+'))b'
exec(@sql)
结果如下:
![](https://i-blog.csdnimg.cn/blog_migrate/20a3bce2ff75130745589269cc5717c8.png)
以上我们实现了在数据不确定的状态下,动态的拼接语句进行了行列互换。