Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help With Pivot

Author  Topic 

JStellato
Starting Member

1 Post

Posted - 2012-12-28 : 12:48:02
This is my first post!

I'm in serious need of help, I have a query that works, it produces this:

UserID UserName GMC Buick Used
1 Jesse 1 2 1
2 Mark 4 3 2

But I need to pivot it, so that it appears this way

Jesse Mark
GMC 1 4
Buick 2 3
Used 1 2

Here is my current query, I'm having a hard time with changing it to a pivot because I can't figure out what the aggregate function needs to be.

declare @Param1 date = '12/1/2012',
@Param2 date = '12/31/2012'

Select Users.UserID, Users.UserName, [GMC] as GMC, [Buick] as Buick, [Used] as Used
From (Users
Left Outer JOIN
(SELECT UserName, Users.UserID, COUNT(Sales.Make) As GMC
FROM Sales
Join SalesUsers On Sales.SaleID = SalesUsers.SaleID
Join Users On SalesUsers.UserID = Users.UserID
WHERE (Sales.PreOwned = 0) And (Sales.Make = 'GMC') AND (Sales.SaleDate <= @Param2) and (Sales.SaleDate >= @Param1)
Group By UserName, Users.UserID)GMCCount
ON Users.UserID = GMCCount.UserID
Left Outer JOIN
(SELECT UserName, Users.UserID, COUNT(Sales.Make) As Buick
FROM Sales
Join SalesUsers On Sales.SaleID = SalesUsers.SaleID
Join Users On SalesUsers.UserID = Users.UserID
WHERE (Sales.PreOwned = 0) And (Sales.Make = 'Buick') AND (Sales.SaleDate <= @Param2) and (Sales.SaleDate >= @Param1)
Group By UserName, Users.UserID)BuickCount
ON Users.UserID = BuickCount.UserID
Left Outer JOIN
(SELECT UserName, Users.UserID, COUNT(Sales.Make) As Used
FROM Sales
Join SalesUsers On Sales.SaleID = SalesUsers.SaleID
Join Users On SalesUsers.UserID = Users.UserID
WHERE (Sales.PreOwned = 1) AND (Sales.SaleDate <= @Param2) and (Sales.SaleDate >= @Param1)
Group By UserName, Users.UserID)UsedCount
ON Users.UserID = UsedCount.UserID)

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-28 : 14:29:20
[code]Select Car,[Jesse],[Mark]
from
(
Select UserName,Car,Value
from Table
UNPIVOT
(Value FOR Car IN
(GMC, Buick, Used)
)AS unpvt
)P
PIVOT
(Max(Value) for UserName in ([Jesse],[Mark])
)AS pvt
[/code]
Go to Top of Page
   

- Advertisement -