How to pivot the table in SQL Server?

Question

Post reply

How to pivot the table in SQL Server?

jenniferhu46

SSCrazy

Points: 2990
More actions
December 9, 2005 at 3:40 pm

#91036

Hi Experts,
I have a Sales_Summary_Table in SQL Server 2000:
Location_2         Measure_Name               Location_1              Measure_Value
District 1058         A CompSales              A Charlotte             13499.9
District 1050         A CompSales              A Charlotte             14220.1
District 1050         A NonCompSales           A Charlotte             0
District 1058         Afternoon Ticket Cnt     A Charlotte             409
District 1050         Breakfast Ticket Cnt     A Charlotte             481
District 1058         Dinner Ticket Cnt        A Charlotte             477
How to write a T-SQL in SQL Server to get the result like the following:
(Location_2 becomes the columns)
Measure_Name          Location 1          District 1058           District 1050
A CompSales             A Charlotte         13499.9                 14220.1
A NonCompSales        A Charlotte          0                          0
Afternoon Ticket Cnt A Charlotte          409                      0
Breakfast Ticket Cnt A Charlotte          0                         481
Dinner Ticket Cnt       A Charlotte          477                       0
Thank you in advance.

Viewing 7 posts - 1 through 6 (of 6 total)

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

PW-201837 SSC-Insane Points: 20805 More actions · Answer 1

Forum search on keyword "Pivot":

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=238656#bm238751

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 2

Jennifer,

Since you want this done in OLTP instead of OLAP, these are more commonly known as "crosstabs" and they run pretty darned fast especially when compared to a cursor and especially when indexed correctly...

SELECT Measure_Name,

Location_1 AS [Location 1],

SUM(CASE WHEN Location_2 = 'District 1058'

THEN Measure_Value ELSE 0 END) AS [District 1058],

SUM(CASE WHEN Location_2 = 'District 1050'

THEN Measure_Value ELSE 0 END) AS [District 1050],

SUM(Measure_Value) AS Total

FROM #yourtable

GROUP BY Measure_Name,Location_1

If you follow the link previously provided, it will eventually lead you to two examples... one's a cursor and one's a single row example... neither of those are my favorite method especially when it comes to performance.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Derrick Leggett SSCrazy Points: 2778 More actions · Answer 3

You can find good crosstab examples by going to http://www.sqlteam.com, clicking on the articles, and searching for either pivot table or cross tab. There are two or three pretty good articles about it.

Derrick Leggett
Mean Old DBA
When life gives you a lemon, fire the DBA.

jenniferhu46 SSCrazy Points: 2990 More actions · Answer 4

Hi Jeff,

Thank you for your reply. But I am sorry that I didn't explain my situation clearly.

The Location_2 has more than 40 different values and it can be changed/added/deleted anytime depend on company's operation decision. Is there any way that I can "crosstabs" the location_2 without using case?

David Jackson SSCertifiable Points: 6414 More actions · Answer 5

Jenifer

Take a look at Jeffs solution for my 'pivot' problem.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=242919

It builds the Case statement for you, & I think is a very elegant solution to this thorny problem.

Dave J

http://glossopian.co.uk/
"I don't know what I don't know."

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 6

David is correct... his link leads to some code I wrote for him to make a "dynamic" cross-tab. Might have a fat-finger here and there but David wrote and posted his actual working solution which is also, pretty cool.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)