Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Denormalizing into a grid Expand / Collapse
Author
Message
Posted Friday, July 12, 2013 9:27 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 03, 2014 11:36 AM
Points: 39, Visits: 133
I just read the article on using UNPIVOT to normalize data. I noted that it said that UNPIVOT doesn't exactly do the reverse of PIVOT, and that's too bad. I guess what a need is DEUNPIVOT. I have several ways to do this for a limited set of cases, but I'm hoping for something that can be generalized.

ie - given this input

DECLARE	@OrderDetail TABLE
(
orderid INT
, personname NVARCHAR(32)
, productdate DATE
, productname VARCHAR(30)
)
-- Load Sample Data
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Jasper', '2013-06-20','Employee Daycare 2 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Jasper', '2013-06-21','Employee Daycare 2 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Jasper', '2013-06-25','Employee Daycare 2 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Jasper', '2013-06-26','Employee Daycare 2 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Jasper', '2013-06-27','Employee Daycare 2 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Jasper', '2013-06-28','Employee Daycare 2 Day' )

INSERT INTO @OrderDetail
VALUES ( 25815983, 'Milo', '2013-06-20','Employee PreSchool 2 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Milo', '2013-06-21','Employee PreSchool 2 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Milo', '2013-06-24','Employee PreSchool 5 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Milo', '2013-06-25','Employee PreSchool 5 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Milo', '2013-06-26','Employee PreSchool 5 Dayy' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Milo', '2013-06-27','Employee PreSchool 5 Day' )
INSERT INTO @OrderDetail
VALUES ( 25815983, 'Milo', '2013-06-28','Employee PreSchool 5 Day' )

I need this output

	        Jasper                  Milo
6/20/2013 Employee Daycare 2 Day Employee Preschool 2 Day
6/21/2013 Employee Daycare 2 Day Employee Preschool 2 Day
6/25/2013 Employee Daycare 4 Day Employee Preschool 5 Day
6/26/2013 Employee Daycare 4 Day Employee Preschool 5 Day
6/27/2013 Employee Daycare 4 Day Employee Preschool 5 Day
6/28/2013 Employee Daycare 4 Day Employee Preschool 5 Day


Such that there is a row for each distinct date, a column for each distinct name and the product name at the intersection of each one. Alternatively, they might ask for distinct names on the rows, products in the columns and dates in the intersections, but I suspect that an solution to one will be extendable to the others.
Post #1473107
Posted Friday, July 12, 2013 9:52 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 2:40 PM
Points: 567, Visits: 3,725
I did it with a Matrix report.
Row: ProductDate
Column: PersonName
Intersection (value): ProductName

or did it have to be in query?
Post #1473123
Posted Friday, July 12, 2013 10:32 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 03, 2014 11:36 AM
Points: 39, Visits: 133
I"m looking at that right now. It looks like it might work in this case. I'd like to be able to do it in a query, too, since I have to return similar data to Excel for some analyses. In this case the report is fine.

Curiously, I'm not getting the Name column header label. The data is there, but the header is blank. I haven't used Matrix reports very much, so it's probably just a matter of beating on it for awhile....
Post #1473142
Posted Friday, July 12, 2013 10:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 2,763, Visits: 5,901
I'm afraid that there's something wrong with your sample data. However, you could do something like this.
SELECT productdate,
MAX(CASE WHEN personname = 'Jasper' THEN productname END) Jasper,
MAX(CASE WHEN personname = 'Milo' THEN productname END) Milo
FROM @OrderDetail
GROUP BY productdate

If you want to make it dynamic, please check the following articles
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473149
Posted Friday, July 12, 2013 11:29 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, February 03, 2014 11:36 AM
Points: 39, Visits: 133
Hmmm. not certain what's wrong with the test data. I can run your query against it and get the expected result.

Be that as it may, however, your code highlights the issue of generalizing the query to handle data that's not known at runtime (i.e. - names, products and dates).

I looked at the articles you linked and they seem to be referring to aggregating numeric data. That's not what I need to do, there's no aggregation going on here.

In another environment, years ago I'd have declared a two dimensional array array(m,n) and put the distinct product dates in the first column starting on the second row and the uniquer names in the top row starting in the second column and then found the array(m,n) index for each rows product and put it there. That's an RBAR solution, though, and SQL Server doesn't have the data structures to support it, anyway.

I'll dig through the articles some more, but they really don't seem to be addressing my needs....
Post #1473160
Posted Friday, July 12, 2013 11:39 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 3:44 PM
Points: 2,763, Visits: 5,901
The output you posted doesn't match with the output of my query using the sample data, but it does what you need.
The articles mention numeric data but it's the same functionality I used on the code I posted. Be sure to understand it well to adapt it to strings as I did using MAX instead of SUM and to be able to do it dynamic if that is what you need.



Luis C.
I am a great believer in luck, and I find the harder I work the more I have of it. Stephen Leacock

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1473171
Posted Friday, July 12, 2013 8:33 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 3,589, Visits: 5,095
If you're learning about UNPIVOT, you might want to look into the CROSS APPLY VALUES approach to UNPIVOT (see first article in my signature links).


My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1473276
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse