行到列 SQL Server [英] Rows to Columns SQL Server

问题描述

我目前正在尝试将行表转换为 MS SQL 数据库中的列视图.我的数据目前在一个表中，如下所示:

I am currently trying to convert a table of rows into a view of columns in an MS SQL Database. My data is currently in a table like so:

ID  OID  Field                                 DataType   Value   Archived
==  ===  ====================================  ========   =====  =========
1   13   E21FC1EC-A6D9-43E2-8C8E-0F2935A7EF68  string     Hello  0
2   13   6BBDE7FA-6F7A-4319-899B-2DF0E6610FA9  string     World  1
3   13   91BB8616-43CD-48EC-97CD-5813B67770ED  int        1      0
4   13   A98D43C3-0A9C-4173-8ECE-29AAAE1D973E  int        2      1
5   13   92BB0DFF-EEAF-4A07-A65A-C3A1E1220F60  boolean    true   0
6   13   1AEE3D08-5F09-4A18-80AC-344E03F6AD7B  boolean    false  0

我的结果集应为每个唯一的 OID 包含一行，并为上表中的每个字段包含一列.值列应该是单元格值.一个例子是:(我在这个例子中减少了列数):

My results set should contain a row for each unique OID, and a column for each field in the above table. The value column should be the cell value. An example would be: (I have reduced the number of columns in this example):

OID E21FC1EC-A6D9-43E2-8C8E-0F2935A7EF68 6BBDE7FA-6F7A-4319-899B-2DF0E6610FA9 91BB8616-43CD-48EC-97CD-5813B67770ED 
=== ==================================== ==================================== ====================================
13  Hello                                World                                1

我尝试了各种方法，包括 CASE、PIVOTS 等，但我不太明白.任何解决方案都应适用于 SQL Server 2005+.

I have tried various approaches, including CASE, PIVOTS etc but I can't quite figure it out. Any solution should work with SQL Server 2005+.

任何帮助将不胜感激.

提前致谢，

大卫

推荐答案

我在这里写了一个动态旋转的好例子:

I am writing here a good example of Dynamic Pivoting:

创建表 [dbo].[UserDetail]( [PK_User] [bigint] NOT NULL,[FK_Property] [int] NOT NULL, [PropValue] varchar NOT NULL )在 [主要]

CREATE TABLE [dbo].[UserDetail]( [PK_User] [bigint] NOT NULL, [FK_Property] [int] NOT NULL, [PropValue] varchar NOT NULL ) ON [PRIMARY]

GO INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue])VALUES (1, 1, N'Jitendra') INSERT [dbo].[UserDetail] ([PK_User],[FK_Property], [PropValue]) VALUES (1, 2, N'Garg') INSERT[dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (2,1, N'Praveen') INSERT [dbo].[UserDetail] ([PK_User], [FK_Property],[PropValue]) VALUES (2, 2, N'Mehta') INSERT [dbo].[UserDetail]([PK_User], [FK_Property], [PropValue]) VALUES (2, 3, N'Udaipur')

GO INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (1, 1, N'Jitendra') INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (1, 2, N'Garg') INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (2, 1, N'Praveen') INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (2, 2, N'Mehta') INSERT [dbo].[UserDetail] ([PK_User], [FK_Property], [PropValue]) VALUES (2, 3, N'Udaipur')

开始

ALTER PROCEDURE [dbo].[usp_Pivot] (@UserId As INT) 作为 DECLARE@Query As VARCHAR(MAX)

ALTER PROCEDURE [dbo].[usp_Pivot] ( @UserId As INT ) As DECLARE @Query As VARCHAR(MAX)

DECLARE @StrColumn As VARCHAR(MAX) = '' SELECT @StrColumn = CASE@StrColumn WHEN '' THEN '' ELSE @StrColumn + ',' END+ 'ISNULL(['+CAST(FK_Property AS VARCHAR(10)) +'],'''') AS Col' + CAST(FK_PropertyAS VARCHAR(10)) FROM dbo.UserDetail(NoLock) WHERE PK_User = @UserId

DECLARE @StrColumn As VARCHAR(MAX) = '' SELECT @StrColumn = CASE @StrColumn WHEN '' THEN '' ELSE @StrColumn + ',' END+ 'ISNULL(['+ CAST(FK_Property AS VARCHAR(10)) +'],'''') AS Col' + CAST(FK_Property AS VARCHAR(10)) FROM dbo.UserDetail(NoLock) WHERE PK_User = @UserId

DECLARE @StrIn As VARCHAR(MAX) = '' SELECT @StrIn = CASE @StrIn WHEN'' THEN '' ELSE @StrIn + ',' END+ '['+ CAST(FK_Property ASVARCHAR(10)) +']' FROM dbo.UserDetail(NoLock) WHERE PK_User = @UserId

DECLARE @StrIn As VARCHAR(MAX) = '' SELECT @StrIn = CASE @StrIn WHEN '' THEN '' ELSE @StrIn + ',' END+ '['+ CAST(FK_Property AS VARCHAR(10)) +']' FROM dbo.UserDetail(NoLock) WHERE PK_User = @UserId

SET @Query = 'SELECT * FROM ( SELECT PK_User As UserId, '+@StrColumn +' FROM ( SELECT PK_User,FK_Property,PropValueFROM dbo.UserDetail(NoLock) WHERE PK_User = ' + CAST(@UserId ASVARCHAR(10)) + ' ) p PIVOT ( MAX (PropValue) FOR FK_Property IN('+ @StrIn +') ) AS pvt )X'

SET @Query = 'SELECT * FROM ( SELECT PK_User As UserId, '+ @StrColumn +' FROM ( SELECT PK_User,FK_Property,PropValue FROM dbo.UserDetail(NoLock) WHERE PK_User = ' + CAST(@UserId AS VARCHAR(10)) + ' ) p PIVOT ( MAX (PropValue) FOR FK_Property IN ('+ @StrIn +') ) AS pvt )X'

打印@Query EXEC (@Query) GO

PRINT @Query EXEC (@Query) GO

您可以根据您的要求修改它.....

You can modify this as per your requirement.....

这篇关于行到列 SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！