Rotate non-aggregate data in SQL Server

Question

this will be the first question I posted here, so I apologize for any inadvertent omissions in etiquette.

In my current project, I took a large, non-standardized table and split it into four separate normalized tables. My ultimate goal with which I refer to this board is to create a view that mimics an unnormalized table for backward compatibility.

To provide a simplified snapshot of my script, the essence of what I'm trying to do is 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 request ...

 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'd like to build 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 using the PIVOT command, but I understand that PIVOT requires some sort of aggregation, and this does not fit my scenario. I also played with the CASE team in the SELECT clause, but that does not smooth my result down to a single record.

Hope someone can shed some light on how I can do this. Let me know if anyone needs more information. Thanks!

Cattle

Answer 1

To get basic data with a numerical role, we can 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 separate row for each role_id that has data! This is not what we want - so we need GROUP BY org_nbr . But then we need to either GROUP BY or fill in all the columns in the SELECT list! The trick then is to come up with an aggregate function that will calm SQL Server and give us the desired results. In this case, MIN will complete the task:

 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 decrease if the maximum role_id increases ...

Answer 2

If you can, I would highly recommend doing this type of rotation in regular code (C #, vb, whatever).

PIVOTing on a SQL server has many drawbacks. Firstly, anything more than 7 or 8 points will massively increase the amount of time that your requests take. Secondly, for this it is required either to execute dynamic sql OR, or to know in advance all potential identifier. Thirdly, it will be difficult to maintain.

The same problems exist in AakashM's answer.

We tried many different ways to make this work pure SQL tuning. For small datasets with very limited pivot, it will work fine. However, the number of role identifiers that you already have is beyond the scope of this.

Instead, just grab the data and create your own table in your favorite language. At this point, either put the data in another sql table or send it where you need it.