tsql joining multiple tables and pivoting specific columns

Question

My data looks like:-

Color Table:-

 Id | Red | Green | Blue | PersonId | MonthId
  1   22      53      4     1         1
  2   52     213    100     3         2
  3    2      32     44     2         3
  4   72     143      4     1         2  ......

Month Table: -

   Id | Name
   1    January  
   2    February 
   3    March    
   4    April    
   5    May    ....

Person Table : -

 Id | Name
  1   Greg  
  2   John  
  3   Gorge ...

I need to produce:-

 Name | January | February | March | April | May
 Greg   22
 Greg   53
 Greg   4
 Greg              72 ....
 John ....

I tried the following case statement but i can't get multiple colors

select p.Name,
       (case when m.Name = 'January' then Red) January
       from Person p, Month m, Color c where c.PersonId = p.Id and c.MonthId = m.Id

I need to get Red, Green and Blue for all months

Pivot just seems a bit too difficult. If someone has any suggestions i'd be glad to try them out.

The data representation may not be 100% correct, i just came up with it to illustrate my point.

Answer 1

You can't use PIVOT for this because you would need to aggregate the color values (the red, blue, green column values) an example of using PIVOT here would be:

WITH Color_Values (ColorValue, PersonId, MonthId)
AS
(
    SELECT
        Red,
        PersonId,
        MonthId
    FROM Color
    UNION ALL
    SELECT
        Green,
        PersonId,
        MonthId
    FROM Color
    UNION ALL
    SELECT
        Blue,
        PersonId,
        MonthId
    FROM Color
),
Data (ColorValue, [Month], Person)
AS
(
    SELECT
        C.ColorValue,
        M.Id AS [Month],
        P.Name AS Person
    FROM [Month] AS M
    RIGHT OUTER JOIN Color_Values AS C
        ON M.Id = C.MonthId
    LEFT OUTER JOIN Person AS P
        ON P.Id = C.PersonID
)
SELECT
    Person AS Name,
    [1] AS Jan,
    [2] AS Feb,
    [3] AS Mar,
    [4] AS Apr,
    [5] AS May
FROM 
(
    SELECT ColorValue, [Month], Person
    FROM Data
) p
PIVOT
(
    COUNT (ColorValue)
    FOR [Month] IN
    ( [1], [2], [3], [4], [5] )
) AS pvt
ORDER BY
    pvt.Person;

But since you want each value to display (3 records for Greg in January instead of 1 record) you want something more like this:

SELECT
    P.Name,
    CASE C.MonthId WHEN 1 THEN C.Red END AS January,
    CASE C.MonthId WHEN 2 THEN C.Red END AS February,
    CASE C.MonthId WHEN 3 THEN C.Red END AS March,
    CASE C.MonthId WHEN 4 THEN C.Red END AS April,
    CASE C.MonthId WHEN 5 THEN C.Red END AS May
FROM Color AS C
INNER JOIN Person AS P
    ON P.Id = C.PersonId

UNION ALL

SELECT
    P.Name,
    CASE C.MonthId WHEN 1 THEN C.Blue END AS January,
    CASE C.MonthId WHEN 2 THEN C.Blue END AS February,
    CASE C.MonthId WHEN 3 THEN C.Blue END AS March,
    CASE C.MonthId WHEN 4 THEN C.Blue END AS April,
    CASE C.MonthId WHEN 5 THEN C.Blue END AS May
FROM Color AS C
INNER JOIN Person AS P
    ON P.Id = C.PersonId

UNION ALL

SELECT
    P.Name,
    CASE C.MonthId WHEN 1 THEN C.Green END AS January,
    CASE C.MonthId WHEN 2 THEN C.Green END AS February,
    CASE C.MonthId WHEN 3 THEN C.Green END AS March,
    CASE C.MonthId WHEN 4 THEN C.Green END AS April,
    CASE C.MonthId WHEN 5 THEN C.Green END AS May
FROM Color AS C
INNER JOIN Person AS P
    ON P.Id = C.PersonId

ORDER BY
    Name,
    January DESC,
    February DESC,
    March DESC,
    April DESC,
    May DESC