比较经典的SQL行转列+分组集联

这是一个比较经典的行转列。用较少的SQL语句，就可以实现行转列。但是此行转列需要根据业务需要，进行二次开发，根据您的需要进行定制和或重写。

下面就简单聊聊这种形式吧

1.建表脚本

CREATE TABLE [dbo].[Table_1](
 [a] [int] NOT NULL,--目标列名
 [b] [uniqueidentifier] NOT NULL,--目标数据
 [c] [nchar](10) NOT NULL--筛选条件
) ON [PRIMARY]

GO
--设置默认值
ALTER TABLE [dbo].[Table_1] ADD  CONSTRAINT [DF_Table_1_b]  DEFAULT (newid()) FOR [b]

2.造数脚本

INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (78, N'f01ac2e9-b5bc-4993-926c-9216203db3f1', N'语文')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (89, N'6c2d753b-36a7-468a-bbf7-4bda8beee4df', N'数学')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (99, N'6093ecd4-7b5b-4225-a6d3-dec213f14a6b', N'自习')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (12, N'6961f7f5-a2b0-45b7-8235-305ad22c2295', N'语文')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (12, N'efdf5e97-4fd9-4c05-bbc0-6f18b38bf2d0', N'语文')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (44, N'116d448d-2cd8-4fdf-a7d8-c8f4f53e8e05', N'数学')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (55, N'cf33dbc1-c265-4835-bb17-f684893ad96c', N'自习')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (66, N'f835c374-4fef-4fd9-8a5b-e16f5feb6bda', N'数学')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (66, N'fb0ce23f-a2bc-4d65-82d3-fb137f414fcf', N'自习')

3.行转列实现-获取‘语文’类行转列数据

declare @0 nvarchar(4000)--定义动态SQL长度(nvarchar默认最大长度为4000)
set @0='select ''Row2Col'' as Demo,''测试分类数据'' as CallBack';--拼接头
--set @0='select '''' as A';
with other as(select a,b from Table_1 where c='语文')--结构化获取数据源
select @0=@0+',['+CAST([a] as nvarchar(20))+']='''+CAST([b] as nvarchar(36))+'''' from other --分页动态拼接核心语句
execute sp_executesql @0 --执行动态拼接的SQL

执行结果如下图

图1

从图上可见，多出2列，分别是【Demo】、【CallBack】。

　　为什么必须多出2列呢？因为这个执行方式，是基于集合动态拼接，所以SQL语句中是以逗号(,)开头。为了确保SQL的可执行，所以必须追加至少一列数据。

我在此处之所以要追加2列，那是因为除了标识这是动态列结果集以外，还要根据数据拼接的业务进行静态值回调。用以判定当前数据的归属分类。我的CallBack列

可以明确告诉我，这个数据，是“测试分类数据”，我拿到数据后，要对值进行二次筛选，并动态构建缓存业务表，和分类映射表。当然，对你来说，可以忽略不计。

　　数据中有重复列？数据中有重复列，我要找12，那么就会造成重名或者目标不明确的现象。那么，我可以非常负责人的说，如果这就是你要的结果，那么你对你的

业务执行，还没有理解好。我们的行转列执行目标除了把数据转过来，还要匹配响应业务。当你执行如上时，请重新考虑下是否有业务分值、数据特例或业务规则分值被

你忽略掉了？

declare @0 nvarchar(4000)
set @0='select ''Row2Col'' as Demo,''测试分类数据'' as CallBack';
--set @0='select '''' as A';
with other as(
select a,b from Table_1
where c='自习'
)
select @0=@0+',['+CAST([a] as nvarchar(20))+']='''+CAST([b] as nvarchar(36))+'''' from other
execute sp_executesql @0

执行结果如下图

图2

上图是行转列的最终执行目标。即数据明确，列名明确，业务实现明确。

4.约束

通过上面2个列子，可以看出。我们的数据源虽然是3列，但实际组建中必须且仅仅能使用数据源中的2列，显示数据源中的多列是无法做到的；要使用行转列，查询【自习】的结果集是比较理想的。当你出现类似于图1时，就必须停下来自习考虑下，数据源是否可用？是否需要进一步细化数据？业务分析有遗漏？还是数据存在冗余现象？

5.提升

当我希望获取所有数据的行转列时，注释掉other结构块中where试试看？

当我希望做分组集联时，我当前的数据就无法满足了。那么重造数据先，造数脚本如下：

DELETE FROM [dbo].[Table_1]--清空原数据，构建出可以执行分组集联的数据
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (78, N'f01ac2e9-b5bc-4993-926c-9216203db3f1', N'语文')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (78, N'6c2d753b-36a7-468a-bbf7-4bda8beee4df', N'数学')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (78, N'6093ecd4-7b5b-4225-a6d3-dec213f14a6b', N'自习')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (12, N'6961f7f5-a2b0-45b7-8235-305ad22c2295', N'语文')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (66, N'efdf5e97-4fd9-4c05-bbc0-6f18b38bf2d0', N'语文')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (12, N'116d448d-2cd8-4fdf-a7d8-c8f4f53e8e05', N'数学')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (12, N'cf33dbc1-c265-4835-bb17-f684893ad96c', N'自习')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (66, N'f835c374-4fef-4fd9-8a5b-e16f5feb6bda', N'数学')
INSERT [dbo].[Table_1] ([a], [b], [c]) VALUES (66, N'fb0ce23f-a2bc-4d65-82d3-fb137f414fcf', N'自习')

执行结果如下图

图3

上图中，数据是根据分组业务形式，构建的数据，如果你的数据源和我建造的数据类似，那么你的数据源是有做分组集联的可能性的。

提供思路如下：

首先根据游标遍历分类数据集合。

其次使用上面的行转列进行数据转换。

最后把目标数据放入临时表中，集联后返回。释放临时表。脚本如下：

declare @0 nvarchar(4000)     
declare @SQL nvarchar(2000)
declare @tableName nvarchar(10)
declare @typeC nvarchar(50)
--定义一个临时表，用来缓存过程数据
CREATE TABLE #MyTemp(--之所以使用显示定义临时表，是由于动态构建临时表时，游标中动态拼接的SQL运行时无法找到动态临时表名！此处可提升。
    [Type] nvarchar(100) NULL,
    [Demo] nvarchar(100) NULL,
    [CallBack] nvarchar(100) NULL,
    [78] [nvarchar](50) NULL,
    [12] [nvarchar](50) NULL,
    [66] [nvarchar](50) NULL
) ON [PRIMARY]
--定义一个表名变量，用于动态拼接SQL时，插入到目标表中 
set @tableName='#MyTemp'
declare myCur cursor for select distinct c from table_1
open myCur
fetch next from myCur into @typeC
while @@FETCH_STATUS=0
begin
    --exec dbo.InsertRow2ColIntoTempTable @typeC,@tableName--我做的可重用存储过程
    set @0='select '''+@typeC+''' as Type,''Row2Col'' as Demo,''测试分类数据'' as CallBack';
    with other as(select a,b from Table_1 where c=@typeC)
    select @0=@0+',['+CAST([a] as nvarchar(20))+']='''+CAST([b] as nvarchar(36))+'''' from other
    select @0='insert into '+@TableName+'([Type],[Demo],[CallBack],[78],[12],[66]) '+@0
    exec sp_executesql @0
    fetch next from myCur into @typeC
end
close myCur--关闭游标
deallocate myCur--释放游标
select * from #MyTemp--返回结果集
drop table #MyTemp--释放临时表

 

执行结果如下图

图4

上图中，就是我需要的最终结果。把数据源中数据进行行转列后，还要根据数据源中筛选类型，进行分组集联，最终呈现给我上图的效果。

此方式有几处可以优化的地方，但是本人能力有限，到文章发布时，没有找到可行性方式。

1.使用动态临时表，构建动态列。这样就能避免由于程序方面的多线程或者死锁，可能造成临时表数据混乱的风险。或后顾之忧。

2.替换掉游标，使用联合查询的方式，以优化执行效率。

3.抽象出存储过程，以提升此解决方式的重用性和通用性。