SQL Server-在3列表上透视两列 [英] SQL Server - Pivot two columns on a 3 column table

问题描述

我对SQL枢轴有些困惑， 我有一个3列的表格，如下所示:

i'm a bit stuck with SQL pivot, I have a 3 column table like this:

Name  Color  Date
TOM   BLUE   10-10-2018
MAT   RED    10-10-2018
TOM   BLUE   11-11-2018

我想要这样的输出:

     10-10-2018  11-11-2018
 TOM  BLUE        BLUE
 MAT  RED         

有人可以帮我吗?

推荐答案

基于问题

Based on the question How to pivot dynamically with date as column with some tweeks for your case:

DECLARE @cols NVARCHAR (MAX);
SELECT @cols = COALESCE (@cols + ',[' + CONVERT(NVARCHAR, [DATE], 121) + ']', 
               '[' + CONVERT(NVARCHAR, [DATE], 121) + ']')
               FROM    (SELECT DISTINCT [DATE] FROM tablename) PV  
               ORDER BY [DATE]

DECLARE @query NVARCHAR(MAX)
SET @query = '           
              SELECT * FROM 
             (
                 SELECT * FROM tablename
             ) x
             PIVOT 
             (
                 MIN(Color)
                 FOR [DATE] IN (' + @cols + ')
            ) p      

            '     
EXEC SP_EXECUTESQL @query;

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