CODEWITHSUNDEEP
sqlsql-serverpivot
user9375339
user9375339

Reputation: 3

SQL Server - Pivot two columns on a 3 column table

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

And I want an output like this:

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

Could anyone help me please?

Upvotes: 0

Views: 134

Answers (2)

iSR5
iSR5

Reputation: 3498

You could do this : 

SELECT *
FROM temp
PIVOT(
 MAX(Color)
  FOR [Date] IN ([10-10-2018], [11-11-2018])  
) PIV
ORDER BY Name DESC

SQLFiddle

IF you want to change NULL fields to empty, just use ISNULL(ColName, '')

LIKE THIS

SELECT PIV.Name, ISNULL(PIV.[10-10-2018],''), ISNULL(PIV.[11-11-2018],'')
FROM temp
PIVOT(
 MAX(Color)
  FOR [Date] IN ([10-10-2018], [11-11-2018])  
) PIV
ORDER BY Name DESC

Upvotes: 1

Pedro Martins
Pedro Martins

Reputation: 874

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;

Upvotes: 1

Related Questions