SQL 行转列常常在具体的业务需要中出现难于处理的情况,使用开发难度增大。故再回顾下行转列的思想与示例。
以下是详细SQL脚本:
IF exists
(
SELECT * from sys.objects where object_id = object_id(N'order_list')
)
drop TABLE order_list
CREATE TABLE [dbo].[order_list](
[OrderID] [int] IDENTITY(1,1) NOT NULL,
[OrderUser] [nvarchar](500) NULL,
[OrderFood] [nvarchar](500) NULL,
CONSTRAINT [PK_order_list] PRIMARY KEY CLUSTERED
(
[OrderID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[order_list] ON
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (1, N'张三', N'苹果')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (2, N'张三', N'桔子')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (3, N'李四', N'苹果')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (4, N'王五', N'香蕉')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (5, N'王五', N'梅子')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (6, N'赵六', N'梅子')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (7, N'麻七', N'苹果')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (8, N'麻七', N'梅子')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (9, N'麻七', N'香蕉')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (10, N'麻七', N'苹果')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (11, N'麻七', N'桔子')
INSERT [dbo].[order_list] ([OrderID], [OrderUser], [OrderFood]) VALUES (12, N'麻七', N'草莓')
SET IDENTITY_INSERT [dbo].[order_list] OFF
/**********************************************************************
作者:董辉
时间:2012-7-19 15:33
类型:新建
说明:sql行转列示例
**********************************************************************/
alter procedure TurnColumn
(
@in_ShowFiled nvarchar(500),--要显示的名称
@in_column nvarchar(500)--要把数据转成列的列名
)
as
begin
declare @QueryFiled nvarchar(max) = '', --用于行转列时的查询字段
@SumFiled nvarchar(max) = '', --用于转列后的聚合计算字段
@sql nvarchar(max) = ''; --用于查询
select @SumFiled += 'sum(t.' + OrderFood + ') ' + OrderFood +',' from order_list group by OrderFood;
set @SumFiled = left(@SumFiled,len(@SumFiled)-1);
select @QueryFiled += OrderFood + ',' from order_list group by OrderFood
set @QueryFiled = left(@QueryFiled,len(@QueryFiled)-1);
set @sql = 'select t.'+@in_ShowFiled+' 姓名,'+@SumFiled+' from
(
select '+@in_ShowFiled+','+@QueryFiled+' from order_list
pivot(count('+@in_column+') FOR '+@in_column+' IN('+@QueryFiled+')) as p
) t
group BY t.'+@in_ShowFiled+'';
exec (@sql);
end
--测试
declare @in_ShowFiled nvarchar(500) = 'OrderUser',
@in_column nvarchar(500) = 'OrderFood';
exec TurnColumn @in_ShowFiled,@in_column