"Pivoting" non-aggregate data in SQL Server

Question

this will be the first question I've posted here so pardon any unintended lapses in board etiquette.

In my current project, I've taken a large, non-normalized table and broken it into four separate, normalized tables. My ultimate goal that I'm reaching out to this board for is to create a view which mimics the non-normalized table for backwards compatibility.

To provide a simplified snapshot of my scenario, the crux of what I'm trying to do lies in two tables:

ASSOC_ROLE          ASSOCIATE
----------          ----------
assoc_id (fk)       assoc_id (pk)
role_id  (fk)       last_name
org_nbr  (fk)

So if I issue the following query...

SELECT Assoc_Role.org_nbr, Assoc_Role.assoc_id, Associate.last_name, Assoc_Role.role_id
FROM Assoc_Role INNER JOIN
     Associate ON Assoc_Role.assoc_id = Associate.assoc_id
WHERE Assoc_Role.org_nbr = '1AA'

...I get the following result set

org_nbr     assoc_id     last_name     role_id
-------     --------     ---------     -------
1AA         1447         Cooper        1
1AA         1448         Collins       3
1AA         1448         Collins       4
1AA         1448         Collins       5
1AA         1449         Lynch         6

Ultimately, the view I would like to construct would look something like this:

org_nbr   role1_ID   role1_name   role2_ID   role2_name   role3_ID   role3_name   role4_ID   role4_name   role5_ID   role5_name   role6_ID   role6_name
-------   --------   ----------   --------   ----------   --------   ----------   --------   ----------   --------   ----------   --------   ----------
1AA       1447       Cooper       NULL       NULL         1448       Collins      1448       Collins      1448       Collins      1449       Lynch

My initial thought was to try to use the PIVOT command, but my understanding is that PIVOT requires some kind of aggregation, and that doesn't fit my scenario. I've also played around with the CASE command in the SELECT clause, but it doesn't flatten my result set down to one record.

Hopefully someone can shed some light on how I can accomplish this. Let me know if anyone needs more info. Thanks!

Scot

Answer 1

To get the basic numbered-role data, we might start with

SELECT
    org_nbr
    , r1.assoc_id   role1_ID
    , r1.last_name  role1_name
    , r2.assoc_id   role2_ID
    , r2.last_name  role2_name
    , r3.assoc_id   role3_ID
    , r3.last_name  role3_name
    , r4.assoc_id   role4_ID
    , r4.last_name  role4_name
    , r5.assoc_id   role5_ID
    , r5.last_name  role5_name
    , r6.assoc_id   role6_ID
    , r6.last_name  role6_name
FROM
    ASSOC_ROLE ar
    LEFT JOIN ASSOCIATE r1 ON ar.role_id = 1 AND ar.assoc_id = r1.assoc_id
    LEFT JOIN ASSOCIATE r2 ON ar.role_id = 2 AND ar.assoc_id = r2.assoc_id
    LEFT JOIN ASSOCIATE r3 ON ar.role_id = 3 AND ar.assoc_id = r3.assoc_id
    LEFT JOIN ASSOCIATE r4 ON ar.role_id = 4 AND ar.assoc_id = r4.assoc_id
    LEFT JOIN ASSOCIATE r5 ON ar.role_id = 5 AND ar.assoc_id = r5.assoc_id
    LEFT JOIN ASSOCIATE r6 ON ar.role_id = 6 AND ar.assoc_id = r6.assoc_id

BUT this will give us, for each org_nbr, a separate row for each role_id that has data! Which is not what we want - so we need to GROUP BY org_nbr. But then we need to either GROUP BY or aggregate over every column in the SELECT list! The trick then is to come up with an aggregate function that will placate SQL Server and give us the results we want. In this case, MIN will do the job:

SELECT
    org_nbr
    , MIN(r1.assoc_id)   role1_ID
    , MIN(r1.last_name)  role1_name
    , MIN(r2.assoc_id)   role2_ID
    , MIN(r2.last_name)  role2_name
    , MIN(r3.assoc_id)   role3_ID
    , MIN(r3.last_name)  role3_name
    , MIN(r4.assoc_id)   role4_ID
    , MIN(r4.last_name)  role4_name
    , MIN(r5.assoc_id)   role5_ID
    , MIN(r5.last_name)  role5_name
    , MIN(r6.assoc_id)   role6_ID
    , MIN(r6.last_name)  role6_name
FROM
    ASSOC_ROLE ar
    LEFT JOIN ASSOCIATE r1 ON ar.role_id = 1 AND ar.assoc_id = r1.assoc_id
    LEFT JOIN ASSOCIATE r2 ON ar.role_id = 2 AND ar.assoc_id = r2.assoc_id
    LEFT JOIN ASSOCIATE r3 ON ar.role_id = 3 AND ar.assoc_id = r3.assoc_id
    LEFT JOIN ASSOCIATE r4 ON ar.role_id = 4 AND ar.assoc_id = r4.assoc_id
    LEFT JOIN ASSOCIATE r5 ON ar.role_id = 5 AND ar.assoc_id = r5.assoc_id
    LEFT JOIN ASSOCIATE r6 ON ar.role_id = 6 AND ar.assoc_id = r6.assoc_id
GROUP BY
    org_nbr

Output:

org_nbr    role1_ID    role1_name role2_ID    role2_name role3_ID    role3_name role4_ID    role4_name role5_ID    role5_name role6_ID    role6_name
---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ---------- ----------- ----------
1AA        1447        Cooper     NULL        NULL       1448        Collins    1448        Collins    1448        Collins    1449        Lynch
Warning: Null value is eliminated by an aggregate or other SET operation.

Of course this will fall short should the maximum role_id increase...