PIVOT Help

Question

PIVOT Help

Viewing 2 posts - 16 through 16 (of 16 total)

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

LutzM SSC Guru Points: 107049 More actions · Answer 1

AlexT (5/14/2009)
Yes, very interesting point of view, but syntax is looks very complicated.
old style do the same and looks much clearer
DECLARE @Table TABLE (CUST_NO INT, PriceRule CHAR(1), Discount numeric(8,2))
INSERT into @Table
SELECT 1234, 'I', 2.30 UNION ALL
SELECT 1234, 'J', 1.55 UNION ALL
SELECT 1234, 'K', 0.99 UNION ALL
SELECT 1234, 'L', 2.25 UNION ALL
SELECT 2345, 'I', 2.40 UNION ALL
SELECT 2345, 'M', 2.15 UNION ALL
SELECT 2345, 'N', 1.15 UNION ALL
SELECT 3456, 'K', 0.99
;with t(i, CUST_NO, PriceRule , Discount ) as(
SELECT ROW_NUMBER() OVER(Partition by CUST_NO ORDER BY CUST_NO,PriceRule),CUST_NO,
PriceRule , Discount
FROM @Table)
SELECT CUST_NO,
max(CASE WHEN i = 1 THEN PriceRule ELSE '' END) AS [Rule1],
SUM(CASE WHEN i = i THEN Discount ELSE 0 END) AS [Disc1],
max(CASE WHEN i = 2 THEN PriceRule ELSE '' END) AS [Rule2],
SUM(CASE WHEN i = 2 THEN Discount ELSE 0 END) AS [Disc2],
max(CASE WHEN i = 3 THEN PriceRule ELSE '' END) AS [Rule3],
SUM(CASE WHEN i = 3 THEN Discount ELSE 0 END) AS [Disc3],
max(CASE WHEN i = 4 THEN PriceRule ELSE '' END) AS [Rule4],
SUM(CASE WHEN i = 4 THEN Discount ELSE 0 END) AS [Disc4],
max(CASE WHEN i = 5 THEN PriceRule ELSE '' END) AS [Rule5],
SUM(CASE WHEN i = 5 THEN Discount ELSE 0 END) AS [Disc5]
FROM T
GROUP BY CUST_NO

Well, that looks similar to the solution posted in http://www.sqlservercentral.com/Forums/FindPost713749.aspx

Doesn't it?;-)

Edit: typo fixed. (Thanx Flo! And I say: Shame, shame, shame, shame, shame! Shame on me!!)

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Florian Reischl SSC-Dedicated Points: 37301 More actions · Answer 2

lmu92 (5/14/2009)
Well, that looks similar to the solution posted in http://www.sqlservercentral.com/Forums/FindPost713749.aspx[
Doesn't it?;-)

Psst, Lutz. Remove the "[" at the end of your link 😉