Transposing rows to columns using self join

Question

I have a table named category with values as below,

CategoryId      | Value |     Flag

1                      25                a
2                      26                a
3                      27                a
1                      28                m
2                      23                m
1                      36                p
2                      33                p

Now I want to transpose the rows present in this table to columns based on the flag, something like

CategoryId      | aValue |     mValue   |     PValue
1                      25                28               36
2                      26                23               33
3                      27                null             null

I am trying to join based on the category id but I am just getting the matched records (inner join) in my resultset even if I use left outer join in my query.

My query:

SELECT 
  A.CategoryId, 
  A.Value AS actual, 
  B.Value AS projected, 
  C.Value AS Manual
FROM ((a AS A left JOIN b AS B ON A.categoryid=B.categoryid) 
  left JOIN c AS C ON A.categoryid=C.categoryid) 
WHERE (((A.flag)="a") and ((B.flag)="p") and ((C.flag) ="m"))

I am getting the proper results if I have the data in 3 different tables.

I just want to check what would be the best way to transpose a rows to column when using self join...

Thanks, Barani

Answer 1

Try this:

SELECT CategoryId, 
       MIN(SWITCH(YourTable.Flag = 'a',Value)) AS aValue,
       MIN(SWITCH(YourTable.Flag = 'm',Value)) AS mValue,
       MIN(SWITCH(YourTable.Flag = 'p',Value)) AS pValue
FROM YourTable
GROUP BY CategoryId