SQL Server 列转行

最新推荐文章于 2024-08-15 22:03:42 发布

开心就是最好

最新推荐文章于 2024-08-15 22:03:42 发布

阅读量690

点赞数

分类专栏： SQL 文章标签： SQL 列转行

本文链接：https://blog.csdn.net/u010678947/article/details/44063083

版权

SQL 专栏收录该内容

20 篇文章 2 订阅

订阅专栏

参照：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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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);
GO
INSERT 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文档格式的字符串