[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

PIVOT in SQL 2005

Posted on 2009-04-28
3
Medium Priority
?
376 Views
Last Modified: 2012-05-06
I am trying to understand the PIVOT syntax for querying in SQL.  I have the following query (see code) which returns the data in a standard flat format.  All the Department Names are listed in the first column (see image), I would like the Department Names to be listed at the top of each column result set.  I understand this can be done with PIVOT, but i have had no luck in coding the query successfully.  I can do it much easier in Access because they have the Cross Tab query type.

Any help is greatly appreciated.
SELECT     TblDepartment_lookup.DepartmentName, SUM(TblFeedback.Score1) AS Expr1, SUM(TblFeedback.Score2) AS Expr2
FROM         TblFeedback INNER JOIN
                      TblUsers ON TblFeedback.FK_User = TblUsers.PK_Users RIGHT OUTER JOIN
                      TblDepartment_lookup ON TblUsers.FK_Department = TblDepartment_lookup.PK_Department
GROUP BY TblDepartment_lookup.DepartmentName

Open in new window

Result-Set.jpg
0
Comment
Question by:szadroga
  • 2
3 Comments
 
LVL 27

Accepted Solution

by:
Chris Luttrell earned 2000 total points
ID: 24255703
Here is a link to a simple PIVOT example from before.  
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Q_24286255.html
Unfortunatelly it looks like you may have two issuses making your question more difficult.  First is that you have two columns you will want to pivot and you can only pivot one at a time, so that has to be solved by doing two pivots and putting them back together with a union.  The second is that I am not sure if your DepartmentName values will be static or dynamic.  If they are dynamic then you have to build parts of the query in text and use dynamic sql.
See if the examples in the link or the simple 1 column and 2 Departments example below provide enough help.
SELECT pvt.[BI], pvt.[Business Analysis]--, pvt.otherDepartmentNames
FROM
(
SELECT     TblDepartment_lookup.DepartmentName, SUM(TblFeedback.Score1) AS Expr1, SUM(TblFeedback.Score2) AS Expr2
FROM         TblFeedback INNER JOIN
                      TblUsers ON TblFeedback.FK_User = TblUsers.PK_Users RIGHT OUTER JOIN
                      TblDepartment_lookup ON TblUsers.FK_Department = TblDepartment_lookup.PK_Department
GROUP BY TblDepartment_lookup.DepartmentName
) as p
PIVOT (SUM(p.Expr1) FOR p.DepartmentName IN ([BI],[Business Analysis]) --, pvt.otherDepartmentNames
) as pvt

Open in new window

0
 
LVL 27

Expert Comment

by:Chris Luttrell
ID: 24322370
Hi szadroga,
Did you have any questions about this?
0
 

Author Closing Comment

by:szadroga
ID: 31575612
Sorry i didnt award earlier, i got tied up with another project and this slipped by
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question