Need help in writing a crosstabs T-SQL!

Question

Need help in writing a crosstabs T-SQL!

jenniferhu46

SSCrazy

Points: 2990
More actions
January 5, 2006 at 9:03 am

#90426

Hi experts,
I am having trouble in writing a crosstabs or 'pivot' T-SQL query from a Sales_Summary_Table in SQL Server 2000 and the sample data as following:
Location_2              Measure_Name          Location_1      Measure_Value
D District 1062         A CompSales            A Charlotte     11831.71
D District 1062        A NonCompSales        A Charlotte     10992.90
D District 1054         A CompSales             A Raleigh        17220.45
D District 1054         A NonCompSales        A Raleigh        17115.60

I need the result:
Measure_Name         Location 1    District 1054_Sum     District 1062_Sum
A CompSales            Eastern        17220.45                 11831.71
A NonCompSales       Eastern        17115.60                 10992.90

But this is the result I am getting from my T-SQL query:
Measure_Name         Location 1 District 1054_Sum District 1062_Sum
A CompSales             Eastern    0                       11831.71
A NonCompSales        Eastern    0                       10992.90
A CompSales             Eastern    17220.45             0
A NonCompSales        Eastern    17115.60             0

Here is my query:
SELECT     Measure_Name, 'Eastern' AS [Location 1],
           SUM(CASE WHEN Location_2 = 'D District 1054' THEN Measure_Value ELSE 0 END) AS [District 1054_Sum],
           SUM(CASE WHEN Location_2 = 'D District 1062' THEN Measure_Value ELSE 0 END) AS [District 1062_Sum]
FROM       micros.Daily_Summary_Table
WHERE     (micros.Daily_Summary_Table.Location_1 IN ('A Charlotte', 'A Raleigh')) AND
                      measure_name IN ('A Compsales', 'A NonCompSales')
GROUP BY Measure_Name, Location_1
PLEASE HELP! Thank you in advance

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

AndrewMurphy SSCertifiable Points: 5586 More actions · Answer 1

There's a generic cross-tabs query available at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=6216

The code has been improved in several later iterations (so you may need to work backwards), and has some limitations, but it seems to do the trick. (It's not mine either)

JacekO SSCertifiable Points: 6610 More actions · Answer 2

You don't need the Location_1 in group by. Because it is the column name in your table this is screwing up your query

---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font]

steve dassin SSCrazy Points: 2396 More actions · Answer 3

The be-all and end-all of pivoting/xtabs on sql server = RAC @

http://www.rac4sql.net

And it's really as easy as its name