代码之家 › 专栏 › 技术社区 › Aarion

SQL使用列名转置数据

unpivot transpose tsql sql-server sql

Aarion · 技术社区 · 7 年前

我试图将SQL Server表中的数据转换为一行数据,但包含多个列,所有列都包含在一个列中,并带有各自的列标题。

**TABLE Column Names:**   Id, ColumnA , ColumnB , ColumnC , StartDate

**Data:**                 1, 'aa' , 'bb' , 'cc', 2016-10-10

所需数据格式:

**ColumnName     Values**
Id               1
ColumnA         aa
ColumnB         bb
ColumnC         cc
StartDate     2016-10-10


CREATE DATABASE ToDelete
GO

USE [ToDelete]
GO

CREATE TABLE [dbo].[sourceData](
    [id] [int] NULL,
    [ColumnA] [varchar](50) NULL,
    [ColumnB] [varchar](50) NULL,
    [ColumnC] [varchar](50) NULL,
    [StartDate] [datetime] NULL
) 
GO
INSERT [dbo].[sourceData] ([id], [ColumnA], [ColumnB], [ColumnC], [StartDate], [EndDate]) VALUES (1, 'aa', N'bb', N'cc', GETDATE())
GO

SELECT c.name
FROM sys.tables t
JOIN sys.columns c
ON t.object_id = c.object_id
WHERE t.name = 'sourceData'

谢谢你的帮助。

非常感谢。

4 回复 | 直到 7 年前

John Cappelletti 7 年前

这里有一个选项可以帮助您创建更像EAV结构的东西

实例

Declare @YourTable Table ([Id] varchar(50),[ColumnA] varchar(50),[ColumnB] varchar(50),[ColumnC] varchar(50),[StartDate] date)
Insert Into @YourTable Values 
 (1,'aa','bb','cc','2016-10-10')


Select A.ID
      ,C.*
 From @YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
                Select Field = a.value('local-name(.)','varchar(100)')
                      ,Value = a.value('.','varchar(max)') 
                 From  B.XMLData.nodes('/row')  as C1(n)
                 Cross Apply C1.n.nodes('./@*') as C2(a)
                 Where a.value('local-name(.)','varchar(100)') not in ('ID','OtherColumnsTo','Exclude')
             ) C

退换商品

ID  Field       Value
1   ColumnA     aa
1   ColumnB     bb
1   ColumnC     cc
1   StartDate   2016-10-10

Prathamesh shinde 7 年前

尝试以下解决方案,我从以下文章中引用来编写此查询: https://www.red-gate.com/simple-talk/sql/t-sql-programming/questions-about-pivoting-data-in-sql-server-you-were-too-shy-to-ask/

USE [ToDelete]
GO
DECLARE @sql AS NVARCHAR(2000);DECLARE @col AS NVARCHAR(2000);
DECLARE @col1 AS NVARCHAR(2000);

SELECT @col = ISNULL(@col + ', ', '') + 
concat('cast(',QUOTENAME(column_name),'as nvarchar(max))',' ',
QUOTENAME(column_name) ),@col1=ISNULL(@col1 + ', ', '') +QUOTENAME(column_name)
FROM (SELECT DISTINCT column_name FROM INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='sourceData') AS Colname;

SET @sql =N'select columnname,[values] from (select '+@col+ ' from dbo.sourceData) as D Unpivot
([values] for columnname in (' + @col1 + '))
as unpiv'

EXEC sp_executesql @sql;

shA.t Rami Jamleh 7 年前

Union all 这样地:

select Id,'ColumnA' columnName, ColumnA  [Values]
from t
union all
select Id,'ColumnB' , ColumnB 
from t
union all
select Id,'ColumnC' , ColumnC
from t
union all
select Id,'StartDate' , cast(StartDate as nvarchar(max))
from t;

SQL Fiddle Demo

Vasily Nidula Nimadith Wijesekara 7 年前

其他变体使用 unpivot :

数据集

DECLARE @YourTable TABLE
([Id]        VARCHAR(10),
 [ColumnA]   VARCHAR(10),
 [ColumnB]   VARCHAR(10),
 [ColumnC]   VARCHAR(10),
 [StartDate] DATE
);
INSERT INTO @YourTable
VALUES
(1, 'aa', 'bb', 'cc', '2016-10-10'),
(2, 'cc', 'dd', 'zz', '2016-10-11');

SELECT Id,
       Field,
       [Value]
FROM
(   SELECT Id,
           ColumnA,
           ColumnB,
           ColumnC,
           CONVERT(VARCHAR(10), StartDate) AS StartDate
    FROM @YourTable
) AS t UNPIVOT([Value] FOR [Field] IN ( ColumnA,
                                        ColumnB,
                                        ColumnC,
                                        StartDate)) up;