SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Help With Pivot
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

JStellato
Starting Member

1 Posts

Posted - 12/28/2012 :  12:48:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/28/2012 :  14:29:20  Show Profile  Reply with Quote
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

Edited by - sodeep on 12/28/2012 14:29:39
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000