SQL Server - Pivot two columns on a 3 column table

Question

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?

score 1 · Accepted Answer · answered Feb 18 '18 at 02:08

1

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;

answered Feb 18 '18 at 02:08

Pedro Martins

854
6
9

Hello Pedro, thank you for the answer, with your code I have 3 rows instead of two because the second register of Tom doesnt group in the first register. How could I do that? – user9375339 Feb 18 '18 at 02:42
Check if Tom you have extra spaces. I've created your scenario and worked perfectly: https://imgur.com/a/p3Evj – Pedro Martins Feb 18 '18 at 03:10
You were right Pedro, now it works perfectly! thanks – user9375339 Feb 18 '18 at 10:43

score 1 · Answer 2 · answered Feb 18 '18 at 03:58

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

SQL Server - Pivot two columns on a 3 column table

2 Answers2

Linked