SQL Server 列转行

来源：互联网发布：王者荣耀延迟高4g网络编辑：程序博客网时间：2024/05/16 15:24

参照：http://www.2cto.com/database/201308/235061.html

Oracle行转列，列转行：http://blog.csdn.net/xiaokui_wingfly/article/details/42419207

1.创建表脚本：

 CREATE TABLE [dbo].[CMS_Vote_Manage]([RID] [int] NOT NULL,[TID] [int] NOT NULL,[IID] [int] NULL,[OtherContent] [nvarchar](50) NULL,[VoteIp] [nvarchar](15) NULL,[VoteTime] [datetime] NULL,[UserNumber] [nvarchar](15) NULL,[SiteID] [nvarchar](12) NULL, CONSTRAINT [PK_NT_vote_Manage] PRIMARY KEY CLUSTERED ([RID] 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

2.数据脚本

INSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8188, 1, 1, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8189, 3, 7, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8190, 4, 9, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8191, 5, 11, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8192, 6, 15, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8193, 7, 19, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8194, 8, 23, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8195, 9, 26, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8196, 10, 31, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8197, 1010, 2017, NULL, '::1', '2015-3-4 11:49:52', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8198, 1, 1, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8199, 3, 7, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8200, 4, 9, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8201, 5, 11, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8202, 6, 15, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8203, 7, 19, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8204, 8, 23, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8205, 9, 26, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8206, 10, 31, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GOINSERT INTO  CMS_Vote_Manage([RID], [TID], [IID], [OtherContent], [VoteIp], [VoteTime], [UserNumber], [SiteID]) VALUES (8207, 1010, 2017, NULL, '::12', '2015-3-4 12:07:38', NULL, NULL);GO

3.转换前数据：

4.希望列转行后的数据：

5.SQL

Declare @sql varchar(max)  set @sql=STUFF((select ','+QUOTENAME([IID]) from (select VoteIp,TID,IID from CMS_Vote_Manage)B group by [IID] FOR XML PATH('')) ,1,1,'')  set @sql='select * from (select VoteIp,TID,IID from CMS_Vote_Manage) t            pivot (sum(TID)for [IID] in ('+@sql+')) a ' exec(@sql)

注释：

（1）SELECT stuff('NBA',1,1,'C')

这个结果就是CBA，该函数主要起到替换字符的作用，第一个参数是待被替换的字符串，第二个参数是替换的起点，第三个参数是从起点开始多少个字符将被替换，第四个参数是要替换的字符串。

（2）QUOTENAME关键字用来给名称加上[]，对于时间必须要加上这个，否则不知道其为列名

（3）FOR XML PATH('')这个可以将查询到的结果集转换为XML文档格式的字符串

0 0