0

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): enter image description here

As mentioned, each employee has 5 strengths: enter image description here

I would like to use PIVOT to generate a table which will ultimately look like this: enter image description here

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..

Joe
  • 797
  • 1
  • 10
  • 23
  • What's the end result? If it's for an excel sheet you distribute, you can connect it to a live datasource and use the result query in a pivot table, which will achieve this grouping/pivoting easily. So, while I'm a fan of growing one's knowledge, this looks like it's final incarnation is a report, and if that is Excel-based, I'd recommend using Excel's functionality rather than SQL's. – Jaaz Cole Jun 23 '14 at 18:33
  • Plan is to create a Web Report.. Editing now. – Joe Jun 23 '14 at 18:40
  • tsql, by the looks of it? – Jaaz Cole Jun 23 '14 at 18:44

2 Answers2

1

Use one of the techniques from this post. For your purposes, you may want to use a delimiter in your column name to the tune of 'StrngthTheme-Strength', which your web report will then parse for the headers.

Community
  • 1
  • 1
Jaaz Cole
  • 3,119
  • 1
  • 15
  • 20
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.

Chipmonkey
  • 863
  • 7
  • 18