SQL Server making rows into columns

Question

I'm trying to take three tables that I have and show the data in a way the user asked me to do it. The tables look like this. (I should add that I am using MS SQL Server)

First Table: The ID is varchar, since it's an ID they use to identify assets and they use numbers as well as letters.

    aID| status | group   |
    -----------------------
    1  |   acti |  group1 |
    2  |   inac |  group2 |
   A3  |   acti |  group1 |

Second Table: This table is fixed. It has around 20 values and the IDs are all numbers

    atID| traitname  |
    ------------------
     1  |   trait1   |
     2  |   trait2   |
     3  |   trait3   |

Third Table: This table is used to identify the traits the assets in the first table have. The fields that have the same name as fields in the above tables are obviously linked.

tID|   aID  |   atID |   trait   |
----------------------------------
1  |   1    |    1   |   NAME    |
2  |   1    |    2   |   INFO    |
3  |   2    |    3   |   GOES    |
4  |   2    |    1   |   HERE    |
5  |   A3   |    2   |   HAHA    |

Now, the user wants the program to output the data in the following format:

aID| status | group  | trait1 | trait2 | trait 3
-------------------------------------------------
1  |  acti  |  group1 |  NAME |  INFO  | NULL
2  |  inac  |  group2 |  HERE |  NULL  | GOES
A3 |  acti  |  group1 |  NULL |  HAHA  | NULL

I understand that to achieve this, I have to use the Pivot command in SQL. However, I've read and tried to understand it but I just can't seem to get it. Especially the part where it asks for a MAX value. I don't get why I need that MAX.

Also, the examples I've seen are for one table. I'm not sure if I can do it with three tables. I do have a query that joins all three of them with the information I need. However, I don't know how to proceed from there. Please, any help with this will be appreciated. Thank you.

Answer 1

There are several ways that you can get the result, including using the PIVOT function.

You can use an aggregate function with a CASE expression:

select t1.aid, t1.status, t1.[group],
  max(case when t2.traitname = 'trait1' then t3.trait end) trait1,
  max(case when t2.traitname = 'trait2' then t3.trait end) trait2,
  max(case when t2.traitname = 'trait3' then t3.trait end) trait3
from table1 t1
inner join table3 t3
  on t1.aid = t3.aid
inner join table2 t2
  on t3.atid = t2.atid
group by t1.aid, t1.status, t1.[group];

See SQL Fiddle with Demo

The PIVOT function requires an aggregate function this is why you would need to use either the MIN or MAX function (since you have a string value).

If you have a limited number of traitnames then you could hard-code the query:

select aid, status, [group],
  trait1, trait2, trait3
from
(
  select t1.aid,
    t1.status,
    t1.[group],
    t2.traitname,
    t3.trait
  from table1 t1
  inner join table3 t3
    on t1.aid = t3.aid
  inner join table2 t2
    on t3.atid = t2.atid
) d
pivot
(
  max(trait)
  for traitname in (trait1, trait2, trait3)
) piv;

See SQL Fiddle with Demo.

If you have an unknown number of values, then you will want to look at using dynamic SQL to get the final result:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(traitname) 
                    from Table2
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT aid, status, [group],' + @cols + ' 
            from 
            (
              select t1.aid,
                t1.status,
                t1.[group],
                t2.traitname,
                t3.trait
              from table1 t1
              inner join table3 t3
                on t1.aid = t3.aid
              inner join table2 t2
                on t3.atid = t2.atid
            ) x
            pivot 
            (
                max(trait)
                for traitname in (' + @cols + ')
            ) p '

execute sp_executesql @query;

See SQL Fiddle with Demo