Sql 多行多列转换
最近项目用到行列转换，总结了一下
转换前后数据结构
转换前： 转换后：
一. 创建表结构，插入数据
--创建数据表 CREATE TABLE [dbo].[RowColumn]( [UserID] [int] NULL, [UserName] [varchar](50) NULL, [Subject1] [int] NULL, [Subject2] [int] NULL, [Subject3] [int] NULL, [Subject4] [int] NULL, [Subject5] [int] NULL ) ON [PRIMARY] --插入时间 insert into RowColumn (UserID,UserName,Subject1,Subject2,Subject3,Subject4,Subject5) values (1,'小明',9,6,8,8,4) insert into RowColumn (UserID,UserName,Subject1,Subject2,Subject3,Subject4,Subject5) values (2,'小王',9,8,7,7,8) insert into RowColumn (UserID,UserName,Subject1,Subject2,Subject3,Subject4,Subject5) values (3,'小田',9,7,5,8,6) insert into RowColumn (UserID,UserName,Subject1,Subject2,Subject3,Subject4,Subject5) values (4,'小強',9,6,7,6,8) insert into RowColumn (UserID,UserName,Subject1,Subject2,Subject3,Subject4,Subject5) values (5,'小明',9,9,6,8,7) insert into RowColumn (UserID,UserName,Subject1,Subject2,Subject3,Subject4,Subject5) values (6,'小強',9,8,8,9,8) insert into RowColumn (UserID,UserName,Subject1,Subject2,Subject3,Subject4,Subject5) values (7,'小王',9,7,9,9,9) insert into RowColumn (UserID,UserName,Subject1,Subject2,Subject3,Subject4,Subject5) values (8,'小王',9,4,6,5,9)
二.固定代码行列转换
--1.先转换Subject1列转换成行 select UserName,Subject1 as 'score','Subject1'as Subject from RowColumn --结果显示 --UserName score Subject ---------------------------------------------------- ----------- -------- --小明 9 Subject1 --小王 9 Subject1 --小田 9 Subject1 --小強 9 Subject1 --小明 9 Subject1 --小強 9 Subject1 --小王 9 Subject1 --小王 9 Subject1 --2.然后把所有的列都转换成行，并联立显示 select UserName,Subject1 as 'score','Subject1'as Subject from RowColumn union all select UserName,Subject2 as 'score','Subject2'as Subject from RowColumn union all select UserName,Subject3 as 'score','Subject3'as Subject from RowColumn union all select UserName,Subject4 as 'score','Subject4'as Subject from RowColumn union all select UserName,Subject5 as 'score','Subject5'as Subject from RowColumn --结果显示 --UserName score Subject ---------------------------------------------------- ----------- -------- --小明 9 Subject1 --小王 9 Subject1 --小田 9 Subject1 --小強 9 Subject1 --小明 9 Subject1 --小強 9 Subject1 --小王 9 Subject1 --小王 9 Subject1 --小明 6 Subject2 --小王 8 Subject2 --小田 7 Subject2 --小強 6 Subject2 --小明 9 Subject2 --小強 8 Subject2 --小王 7 Subject2 --小王 4 Subject2 --小明 8 Subject3 --小王 7 Subject3 --小田 5 Subject3 --小強 7 Subject3 --小明 6 Subject3 --小強 8 Subject3 --小王 9 Subject3 --小王 6 Subject3 --小明 8 Subject4 --小王 7 Subject4 --小田 8 Subject4 --小強 6 Subject4 --小明 8 Subject4 --小強 9 Subject4 --小王 9 Subject4 --小王 5 Subject4 --小明 4 Subject5 --小王 8 Subject5 --小田 6 Subject5 --小強 8 Subject5 --小明 7 Subject5 --小強 8 Subject5 --小王 9 Subject5 --小王 9 Subject5 --3.然后再行列转换再把UserName行转换成列，按Subject分组合计 select Subject ,Sum(case userName when '小王' then score else 0 end) as '小王' ,Sum(case userName when '小強' then score else 0 end) as '小強' ,Sum(case userName when '小田' then score else 0 end) as '小田' ,Sum(case userName when '小明' then score else 0 end) as '小明' from ( select UserName,Subject1 as 'score','Subject1'as Subject from RowColumn union all select UserName,Subject2 as 'score','Subject2'as Subject from RowColumn union all select UserName,Subject3 as 'score','Subject3'as Subject from RowColumn union all select UserName,Subject4 as 'score','Subject4'as Subject from RowColumn union all select UserName,Subject5 as 'score','Subject5'as Subject from RowColumn ) U group by Subject --结果显示 --Subject 小王 小強 小田 小明 ---------- ----------- ----------- ----------- ----------- --Subject1 27 18 9 18 --Subject2 19 14 7 15 --Subject3 22 15 5 14 --Subject4 21 15 8 16 --Subject5 26 16 6 11
三.动态sql生成多行多列转换（表中动态增加行列，动态转换）
declare @conSql varchar(8000) declare @conSql_01 varchar(8000) declare @conSql_02 varchar(8000) set @conSql='' set @conSql_01='' set @conSql_02='' set @conSql='select Subject' --利用select from Table 的形式遍历要用列转行的字段 select @conSql=@conSql+',Sum(case userName when '''+userName+''' then score else 0 end ) as '''+userName+'''' --distinct 去除重复行 from (select distinct userName from RowColumn)U --利用select from syscolumns 的形式 遍历由列转换成行的字段 select @conSql_01=@conSql_01+'select UserName,'+name+' as ''score'','''+name+''' as Subject from RowColumn union all ' --where条件 排除不需要转换的字段 from syscolumns where id=object_id('RowColumn')and name<>'UserID' and name<>'UserName' --去除字符串最后一个 union all select @conSql_01=Substring(@conSql_01,1,len(@conSql_01)-9) --字符串拼接 按Subject分组 set @conSql_02=@conSql+'from ( '+@conSql_01+' ) U group by Subject' exec(@conSql_02) --结果显示 --Subject 小王 小強 小田 小明 ---------- ----------- ----------- ----------- ----------- --Subject1 27 18 9 18 --Subject2 19 14 7 15 --Subject3 22 15 5 14 --Subject4 21 15 8 16 --Subject5 26 16 6 11
