[Solved] Unpivot?
-
Hi all,
I'm trying to turn Table A into Table B with a query.
I'm studying how to use unpivot as this exercise seems a lot like an unpivot case, but I can't seem to get the desired result.
Can anyone give some feedback or ideas on how to achieve Table B?
Below are the queries to create and populate Table A:
CREATE TABLE TableA (
CustomerCode VARCHAR (255),
ProductCode VARCHAR (255),
"Date" VARCHAR (255),
Discount VARCHAR (255)
)
;INSERT INTO TableA
(CustomerCode, ProductCode, "Date", Discount)
VALUES
('AAA','111','20170111','0.15'),
('AAA','111','20170111','0.2'),
('AAA','111','20171101','0.1'),
('BBB','222','20170120','0.05'),
('BBB','222','20170120','0.2')Any ideas would be highly appreciated 🙂
-
The 3rd insert to your test data doesn't match your graphics so I made a change to the test data.
CREATE TABLE dbo.TableA
(
CustomerCode VARCHAR (255)
,ProductCode VARCHAR (255)
,[Date] VARCHAR (255)
,Discount VARCHAR (255)
)
;
GO
INSERT INTO TableA
(CustomerCode, ProductCode, "Date", Discount)
VALUES
('AAA','111','20170111','0.15'),
('AAA','111','20170111','0.2'),
('AAA','111','20170111','0.1'),
('BBB','222','20170120','0.05'),
('BBB','222','20170120','0.2')
;To learn the "CROSSTAB" method used in the following code, please see the following article.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
WITH cteEnumerate AS
(
SELECT CustomerCode
,ProductCode
,[Date]
,DisCount
,DisCount# = ROW_NUMBER() OVER (PARTITION BY CustomerCode, ProductCode, [Date] ORDER BY Discount)
FROM dbo.TableA
)
SELECT CustomerCode
,ProductCode
,[Date]
,Discount1 = MIN(CASE WHEN DisCount# = 1 THEN Discount ELSE 'N/A' END)
,Discount2 = MIN(CASE WHEN DisCount# = 2 THEN Discount ELSE 'N/A' END)
,Discount3 = MIN(CASE WHEN DisCount# = 3 THEN Discount ELSE 'N/A' END)
,Discount4 = MIN(CASE WHEN DisCount# = 4 THEN Discount ELSE 'N/A' END)
,Discount5 = MIN(CASE WHEN DisCount# = 5 THEN Discount ELSE 'N/A' END)
FROM cteEnumerate
GROUP BY CustomerCode
,ProductCode
,[Date]
ORDER BY CustomerCode
,ProductCode
,[Date]
;And, just to be sure, what you've asked for is a "PIVOT". Converting TableB to TableA would be an "UNPIVOT".
--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) -
Thank you so much Jeff Moden! I really really appreciate it 🙂
Apologies for the typo on the insert.
And thank you for the article too, it will be very useful to me 😀 Already bookmarked it
- Vegeta7 - Sunday, October 1, 2017 4:32 PM
Glad to help and thank you for the kind feedback.
--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)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply