SQL PIVOT, JOIN, and aggregate function to generate report

Question

I am working on creating a report which will incorporate data across 4 different tables. For this question, I have consolidated the data into 2 tables and am stuck trying to figure out exactly how to create this report using PIVOT.

The report will hold the top 5 strengths of an employee based on the Clifton StrengthsFinder assessment.

This is the table with the Names of the Clifton Strengths (34 rows total):

As mentioned, each employee has 5 strengths:

I would like to use PIVOT to generate a table which will ultimately look like this:

With a twist, I don't need the Team Name as a Row, it should be a column. The Count at the bottom and Themes at the top (Executing, Influencing, etc) can be ignored.

The columns of the table I'm trying to output are PersonFk, PersonName, TeamName, Achiever, Arranger, etc... (34 Strengths) and each row of the table with Values (personfk, name, team, 1 if person has the strength, 0 otherwise). This table should be SQL, not excel (sorry, just the best example I have on hand without spending an hour learning how to use Paint or something).

I'm not very familiar with aggregate functions, and am just now getting into the more complex SQL queries..

Answer 1

Interesting. Pivot requires an aggregate function to build the 1-5 values, so you'll have to rewrite your inner query probably as a union, and use MAX() as a throwaway aggregate function (throwaway because every record should be unique, so MAX, MIN, SUM, etc. should all return the same value:

SELECT * INTO #newblah from (
   SELECT PersonFK, 1 as StrengthIndex, Strength1 as Strength from blah UNION ALL
   SELECT PersonFK, 2 as StrengthIndex, Strength2 as Strength from blah UNION ALL
   SELECT PersonFK, 3 as StrengthIndex, Strength3 as Strength from blah UNION ALL
   SELECT PersonFK, 4 as StrengthIndex, Strength4 as Strength from blah UNION ALL
   SELECT PersonFK, 5 as StrengthIndex, Strength5 as Strength from blah
 )

Then

select PersonFK, [Achiever], [Activator], [Adaptability], [Analytical], [Belief] .....
from
(
  select PersonFK, StrengthIndex, Strength
  from #newblah
) pivotsource
pivot
(
  max(StrengthIndex)
  for Strength in ([Achiever], [Activator], [Adaptability], [Analytical], [Belief] ..... )
) myPivot;

The result of that query should be able to be joined back to your other tables to get the Person name, Strength Category, and Team name, so I'll leave that to you. You don't HAVE to do the first join as a temporary table -- you could do it as a subselect inline, so this could all be done in one SQL query, but that seems painful if you can avoid it.