SQL 实现行列转换示例

来源：互联网发布：ubuntu安装matlab.iso 编辑：程序博客网时间：2024/05/15 07:41

/**********************************************************************动态日期行转列**********************************************************************************编写： BY 轮子******2013-04-12*********************************************************************/-- 含日期的记录实现以日期为列动态按生产日期显示--创建测试数据库USE [Office] GO/****** Object:  Table [dbo].[test03]    Script Date: 04/12/2013 10:36:42 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOSET ANSI_PADDING ONGOCREATE TABLE [#test03]([序号] [varchar](50) NULL,[日期] [datetime] NULL,[计划单号] [varchar](50) NULL,[客户] [varchar](50) NULL,[产品名称] [varchar](50) NULL,[实际产出量] [int] NULL,[单位] [varchar](50) NULL,[分类] [varchar](50) NULL,[工序] [varchar](50) NULL) ON [PRIMARY]GOSET ANSI_PADDING OFFGO--插入测试数据INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'622', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13S03-026', N'FH09-B门页', 5, N'扇', N'门扇', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'623', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13y03-036', N'门套板', 5, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'624', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13y03-036', N'门套线', 30, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'625', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13y03-032', N'FM02-门页', 5, N'扇', N'门扇', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'626', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13y03-032', N'门套板', 5, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'627', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13S02-010', N'门套线', 30, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'628', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s02-010', N'底座', 2, N'个', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'629', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s02-010', N'上门套线', 2, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'630', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s02-010', N'门套板', 12, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'631', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s02-010', N'门套线', 80, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'632', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s02-010', N'门头', 5, N'个', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'633', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13S03-026', N'门套板', 1, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'634', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-026', N'门套线', 12, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'635', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-014', N'门套板', 2, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'636', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-014', N'门套线', 12, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'637', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-014', N'门套板', 5, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'638', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-020', N'门套线', 63, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'639', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-020', N'双面套', 3, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'640', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-020', N'单面套', 5, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'641', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-021', N'YG10门页', 5, N'扇', N'门扇', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'642', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-022', N'门套板', 5, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'643', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-022', N'门套线', 25, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'644', CAST(0x0000A19300000000 AS DateTime), N'00:00:00.000', N'13s03-022', N'门头', 5, N'个', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'645', CAST(0x0000A19400000000 AS DateTime), N'00:00:00.000', N'13s03-022', N'YG02门页', 5, N'扇', N'门扇', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'646', CAST(0x0000A19400000000 AS DateTime), N'00:00:00.000', N'13Y02-010', N'门套板', 5, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'647', CAST(0x0000A19400000000 AS DateTime), N'00:00:00.000', N'13Y02-010', N'门套线', 30, N'根', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'648', CAST(0x0000A19400000000 AS DateTime), N'00:00:00.000', N'13Y02-010', N'双开门', 1, N'扇', N'门扇', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'649', CAST(0x0000A19400000000 AS DateTime), N'00:00:00.000', N'13Y02-010', N'木饰面', 1, N'块', N'其他', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'650', CAST(0x0000A19500000000 AS DateTime), N'00:00:00.000', N'13Y02-010', N'门套板', 1, N'套', N'门套', N'木工')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'651', CAST(0x0000A19500000000 AS DateTime), N'00:00:00.000', N'13Y02-010', N'门页', 5, N'扇', N'门扇', N'油漆')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'652', CAST(0x0000A19500000000 AS DateTime), N'00:00:00.000', N'13Y02-010', N'门页', 5, N'扇', N'门扇', N'油漆')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'653', CAST(0x0000A19500000000 AS DateTime), N'00:00:00.000', N'13S03-026', N'门页', 2, N'扇', N'门扇', N'油漆')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'654', CAST(0x0000A19500000000 AS DateTime), N'00:00:00.000', N'13S03-026', N'门页', 2, N'扇', N'门扇', N'油漆')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'655', CAST(0x0000A19600000000 AS DateTime), N'00:00:00.000', N'13S03-026', N'门页', 5, N'扇', N'门扇', N'油漆')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'656', CAST(0x0000A19600000000 AS DateTime), N'00:00:00.000', N'13S2-010', N'门套线', 30, N'根', N'其他', N'油漆')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'657', CAST(0x0000A19600000000 AS DateTime), N'00:00:00.000', N'13S2-010', N'门套板', 30, N'套', N'门套', N'油漆')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'658', CAST(0x0000A19700000000 AS DateTime), N'00:00:00.000', N'12S12-011', N'门页', 18, N'扇', N'门扇', N'包装')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'659', CAST(0x0000A19700000000 AS DateTime), N'00:00:00.000', N'13S3-017', N'门套', 18, N'套', N'门套', N'包装')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'660', CAST(0x0000A19700000000 AS DateTime), N'00:00:00.000', N'12Y12-011', N'门页', 4, N'扇', N'门扇', N'包装')INSERT [#test03] ([序号], [日期], [计划单号], [客户], [产品名称], [实际产出量], [单位], [分类], [工序]) VALUES (N'661', CAST(0x0000A19800000000 AS DateTime), N'00:00:00.000', N'12Y12-011', N'门套', 4, N'套', N'门套', N'包装')----

-- 显示记录select * from [#test03] -- 以日期条件作为统计字段declare @strSQL nvarchar(4000)set @strSQL=''Select@strSQL = @strSQL + ',' + QUOTENAME( convert(varchar(10),[日期],120)) +'= sum( case when convert(varchar(10),[日期],120) ='+QUOTENAME( convert(varchar(10),[日期],120),'''')+' then [实际产出量] else 0 end)' + CHAR(10)from #test03 --where  [日期] >= '2013-04-1' -- group BY convert(varchar(10),[日期],120) order by convert(varchar(10),[日期],120)print '--行列转换查询动态列'print @strSQL /*-- QUOTENAME -示例  select QUOTENAME( convert(varchar(10),GETDATE (),120)),QUOTENAME( convert(varchar(10),GETDATE (),120),'''')--*/-- 实现行列转换set @strSQL='SELECT'+ CHAR (10) + ' [工序]'+ CHAR (10) + ',[分类]'+ CHAR (10) + @strSQL+ CHAR (10) + ',[合计]=sum( [实际产出量])  '+ CHAR (10) + 'FROM [#test03] a  '+ CHAR (10) + ' --WHERE [日期] >= ''2013-04-1'' '+ char(10) + 'GROUP BY  分类,工序' exec(@strSQL) print '--行列转换查询语句'print @strSQL /*-- 动态生成日期列的查询语句SELECT [工序],[分类],[2013-04-01]= sum( case when convert(varchar(10),[日期],120) ='2013-04-01' then [实际产出量] else 0 end),[2013-04-02]= sum( case when convert(varchar(10),[日期],120) ='2013-04-02' then [实际产出量] else 0 end),[2013-04-03]= sum( case when convert(varchar(10),[日期],120) ='2013-04-03' then [实际产出量] else 0 end),[2013-04-04]= sum( case when convert(varchar(10),[日期],120) ='2013-04-04' then [实际产出量] else 0 end),[合计]=sum( [实际产出量])  FROM [#test03] a   --WHERE [日期] >= '2013-04-1' GROUP BY  分类,工序--*/  drop table [#test03]

/*--结果工序分类2013-04-012013-04-022013-04-032013-04-04合计包装门扇0002222包装门套0002222木工门扇1560021木工门套4351049木工其他2663100297油漆门扇0014519油漆门套0003030油漆其他0003030--*/

以上来自论坛帖子，觉得很不错，做一下注解，作为范例和大家分享