文章/答案/技术大牛

发布

问透视/取消透视的帮助
EN

Stack Overflow用户

提问于 2010-03-05 08:34:24

回答 3查看 1.4K关注 0票数 0

我有一个表格，如下

Name    Priority    Date    
-------------------------
A         2          d1
B         3          d2

如何编写查询以实现以下输出

ColumnNames   d1      d2
--------------------------
 Name         A       B
 Priority     2       3

谢谢

sql-server-2008

pivot

sql

sql-server

tsql

回答 3

Stack Overflow用户

回答已采纳

发布于 2010-03-05 08:43:52

以下是我承诺的解决方案：

编辑：我修改了我的答案，以回答操作员提出的其他问题。

需要注意的几件事：

STUFF函数:这个函数用于将XML字符串转换为常规字符串(并删除第一个逗号)

By (我从OMG那里偷来的)：你需要这样做，以确保你没有任何重复的日期

在你运行这个之前，请确保表中没有太多的日期。太多的列可以是一个problem
NVARCHAR:我用这个代替了@sql变量的VARCHAR，因为sp_ExecuteSQL需要it.
CONVERT(VARCHAR，DateColumn，101)：我这样做是因为除非你把日期转换成一个字符串，否则这是行不通的。101的结果是: mm/dd/yyyy，但是您可以使用所需的任何内容(确保在此脚本中使用的2次匹配)
为了对多个列起作用，您必须首先使用NVARCHAR(MAX))

并将所有列转换为相同的数据类型(更多信息在代码下面)

重要的是要注意，为了连接字符串，它们必须具有相同的数据类型和大小(在我的示例中，它们都是yyyy

有关将日期转换为字符串的更多信息，请阅读this page。

话虽如此，代码如下：

-- table with multiple columns
CREATE TABLE #TBL ( 
    NameColumn VARCHAR(10), 
    PriorityColumn INT,
    AnotherColumn FLOAT,
    DateColumn DATETIME 
)


-- Insert the test data
INSERT INTO #TBL VALUES ('a', 1, 7.2, '1/1/2000')
INSERT INTO #TBL VALUES ('a', 2, 8.9, '1/2/2000')
INSERT INTO #TBL VALUES ('a', 2, 53.2, '1/3/2000')
INSERT INTO #TBL VALUES ('a', 3, 9.12, '1/4/2000')
INSERT INTO #TBL VALUES ('b', 2, 1.26, '1/1/2001')


DECLARE
    @sql NVARCHAR(max),
    @dates NVARCHAR(max)


-- I separated this to make the code easier to read
SET @dates = STUFF(
    (
        SELECT N',[' + CONVERT(VARCHAR, DateColumn, 101) + ']' AS [text()]
        FROM #TBL
        GROUP BY DateColumn
        ORDER BY DateColumn
        FOR XML PATH('')
    ), 1, 1, N''
)


-- I will break this part of the code up below
SET @sql = N'SELECT
    *
FROM (
    SELECT
        ColumnName,
        ColumnValue,
        CONVERT(VARCHAR, DateColumn, 101) AS DateString
    FROM (
        SELECT
            CAST(NameColumn AS VARCHAR(100)) AS NameColumn,
            CAST(PriorityColumn AS VARCHAR(100)) AS PriorityColumn,
            CAST(AnotherColumn AS VARCHAR(100)) AS AnotherColumn,
            DateColumn
        FROM #TBL
    ) P
    UNPIVOT (
        ColumnValue
        FOR ColumnName IN (NameColumn, PriorityColumn, AnotherColumn)
    ) UNPIV
) P2
PIVOT (
    MAX(ColumnValue)
    FOR DateString IN (' + @dates + N')
) PIV'

EXECUTE dbo.sp_ExecuteSQL @sql


DROP TABLE #TBL

让我们简单地讲一遍

-- I first do an UNPIVOT on all of the columns I want to pivot on, at the same time, converting them to the same datatype
SELECT
    ColumnName,
    ColumnValue,
    CONVERT(VARCHAR, DateColumn, 101) AS DateString
FROM (
    SELECT
        CAST(NameColumn AS VARCHAR(100)) AS NameColumn,
        CAST(PriorityColumn AS VARCHAR(100)) AS PriorityColumn,
        CAST(AnotherColumn AS VARCHAR(100)) AS AnotherColumn,
        DateColumn
    FROM #TBL
) P
UNPIVOT (
    ColumnValue
    FOR ColumnName IN (NameColumn, PriorityColumn, AnotherColumn)
) UNPIV

一旦我这样做了，数据将如下所示：

ColumnName  ColumnValue DateString
----------------------------------
NameColumn      a       01/01/2000
PriorityColumn  1       01/01/2000
AnotherColumn   7.2     01/01/2000
NameColumn      a       01/02/2000
PriorityColumn  2       01/02/2000
AnotherColumn   8.9     01/02/2000
NameColumn      a       01/03/2000
PriorityColumn  2       01/03/2000
AnotherColumn   53.2     01/03/2000
NameColumn      a       01/04/2000
PriorityColumn  3       01/04/2000
AnotherColumn   9.12     01/04/2000
NameColumn      b       01/01/2001
PriorityColumn  2       01/01/2001
AnotherColumn   1.26     01/01/2001

然后我们可以像这样使用PIVOT来获得我们需要的所有列：

PIVOT (
    MAX(ColumnValue)
    FOR DateString IN (' + @dates + N')
) PIV

票数 3

Stack Overflow用户

发布于 2010-03-05 09:27:04

这就是你要找的：

create table NameAndDate (NameCol varchar(200), DateCol datetime);

insert into NameAndDate (NameCol, DateCol) values ('A', '2010-03-04');
insert into NameAndDate (NameCol, DateCol) values ('B', '2010-03-05');

select * from NameAndDate;

select * from NameAndDate
pivot (
max(NameCol) 
for DateCol 
in ([2010-03-04], [2010-03-05])) 
as PivotResults;

这给了我以下结果：

   NameCol     DateCol
---------------------------------------
1  A           2010-03-04 00:00:00.0000
2  B           2010-03-05 00:00:00.0000


   2010-03-04  2010-03-05
-------------------------
1  A           B

注这要求您在编写查询时提前知道日期(除非您使用Gabriel提到的动态SQL )。

编辑：

我尝试使用OMG Ponies的方法，但不得不像这样修改它：

declare @Dates nvarchar(max)
set @Dates = 
    (select '['+ convert(varchar, NameAndDate.DateCol) + '],' 
    from NameAndDate
    group by NameAndDate.DateCol
    order by NameAndDate.DateCol
    for xml path(''))
set @Dates = left(@Dates, len(@Dates) - 1)

declare @SQL nvarchar(4000)
set @SQL = 
    'select *  
    from NameAndDate
    pivot (
    max(NameCol) 
    for DateCol in (' + @Dates + ')) 
    as PivotResults'

exec sp_executesql @SQL

这给了我以下结果：

   Mar 4 2010 12:00AM   Mar 5 2010 12:00AM
------------------------------------------
1  A                    B

票数 2

Stack Overflow用户

发布于 2010-03-05 10:49:35

使用：

DECLARE @SQL NVARCHAR(4000)
DECLARE @dates NVARCHAR(max)

SET @dates = SELECT '['+ t.date +"],"
               FROM TABLE t
           GROUP BY t.date
           ORDER BY t.date
            FOR XML PATH('')

@SQL = 'SELECT * 
          FROM TABLE 
         PIVOT(MAX(name) FOR date IN (@dates)) AS pvt'

BEGIN 

  EXEC sp_executesql @SQL, N'@dates NVARCHAR(max)', @dates

END

票数 2

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/2383769

复制

相似问题

问透视/取消透视的帮助
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问透视/取消透视的帮助EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问透视/取消透视的帮助
EN