SQL PIVOT, JOIN, and aggregate function to generate report -

SQL PIVOT, JOIN, and aggregate function to generate report -

i working on creating study incorporate info across 4 different tables. question, have consolidated info 2 tables , stuck trying figure out how create study using pivot.

the study hold top 5 strengths of employee based on clifton strengthsfinder assessment.

this table names of clifton strengths (34 rows total):

as mentioned, each employee has 5 strengths:

i utilize pivot generate table this:

with twist, don't need team name row, should column. count @ bottom , themes @ top (executing, influencing, etc) can ignored.

the columns of table i'm trying output personfk, personname, teamname, achiever, arranger, etc... (34 strengths) , each row of table values (personfk, name, team, 1 if person has strength, 0 otherwise). this table should sql, not excel (sorry, best illustration have on hand without spending hr learning how utilize paint or something).

i'm not familiar aggregate functions, , getting more complex sql queries..

interesting. pivot requires aggregate function build 1-5 values, you'll have rewrite inner query union, , utilize max() throwaway aggregate function (throwaway because every record should unique, max, min, sum, etc. should homecoming same value:

select * #newblah ( select personfk, 1 strengthindex, strength1 strength blah union select personfk, 2 strengthindex, strength2 strength blah union select personfk, 3 strengthindex, strength3 strength blah union select personfk, 4 strengthindex, strength4 strength blah union select personfk, 5 strengthindex, strength5 strength blah )

then

select personfk, [achiever], [activator], [adaptability], [analytical], [belief] ..... ( select personfk, strengthindex, strength #newblah ) pivotsource pivot ( max(strengthindex) strength in ([achiever], [activator], [adaptability], [analytical], [belief] ..... ) ) mypivot;

the result of query should able joined other tables person name, strength category, , team name, i'll leave you. don't have first bring together temporary table -- subselect inline, done in 1 sql query, seems painful if can avoid it.

sql pivot unpivot