T-SQL using PIVOT to display data

swaggrK
swaggrK used Ask the Experts™
on
I need to create a join that displays EmployeeID and Rate based on the AttendantCount
Currently the Rate and AttendantCount are displayed as rows but I need to display the data horizontal.
I was told this could possibly be done using PIVOT in t-sql.
I have attached a screenshot of what the result should look like

The CommissionLevelDetail table contains CommissionLevelID, AttendantCount, Rate
The CommissionLevelAssignment table contains CommissionLevelID, EmployeeID


Screenshot of expected resultstbl_CommissionLevelAssignment.xlsxtbl_CommissionLevelDetail.xlsx
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Topic Advisor, Page Editor
Distinguished Expert 2018
Commented:
It looks as if the tables are joined via  CommissionlevelID

And assume the aggregation for the PIVOT is a SUM(RATE)

Given the above, can certainly do :
select Employeeid,isnull([1],0) as Group1 ,isnull([2],0) as Group2,isnull([3],0) as Group3,isnull([4],0) as Group4,isnull([5],0) as Group5,isnull([6],0) as Group6,isnull([7],0) as Group7,isnull([8],0) as Group8,isnull([9],0) as Group9,isnull([10],0) as Group10
from 
( select A.Employeeid, D.Rate, D.AttendantCount
  from tbl_CommissionLevelAssignment A
  inner join tbl_CommissionLevelDetail D on A.CommissionlevelID = D.CommissionLevelID ) SRC
  PIVOT
  (sum(rate) for AttendantCount in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10])) pvt

Open in new window

And to help explain PIVOT, I have written articles - it does get into dynamic SQL, but the first part does explain the pivot.
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/A_653-Dynamic-Pivot-Procedure-for-SQL-Server.html

Author

Commented:
Thanks Mark...this was very useful.
Mark WillsTopic Advisor, Page Editor
Distinguished Expert 2018

Commented:
My pleasure, and very pleased you found it useful :)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial