July 12, 2013 at 9:27 am
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
INSERTINTO @OrderDetail
VALUES( 25815983, 'Jasper', '2013-06-20','Employee Daycare 2 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Jasper', '2013-06-21','Employee Daycare 2 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Jasper', '2013-06-25','Employee Daycare 2 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Jasper', '2013-06-26','Employee Daycare 2 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Jasper', '2013-06-27','Employee Daycare 2 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Jasper', '2013-06-28','Employee Daycare 2 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Milo', '2013-06-20','Employee PreSchool 2 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Milo', '2013-06-21','Employee PreSchool 2 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Milo', '2013-06-24','Employee PreSchool 5 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Milo', '2013-06-25','Employee PreSchool 5 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Milo', '2013-06-26','Employee PreSchool 5 Dayy' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Milo', '2013-06-27','Employee PreSchool 5 Day' )
INSERTINTO @OrderDetail
VALUES( 25815983, 'Milo', '2013-06-28','Employee PreSchool 5 Day' )
I need this output
Jasper Milo
6/20/2013Employee Daycare 2 DayEmployee Preschool 2 Day
6/21/2013Employee Daycare 2 DayEmployee Preschool 2 Day
6/25/2013Employee Daycare 4 DayEmployee Preschool 5 Day
6/26/2013Employee Daycare 4 DayEmployee Preschool 5 Day
6/27/2013Employee Daycare 4 DayEmployee Preschool 5 Day
6/28/2013Employee Daycare 4 DayEmployee 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.
July 12, 2013 at 9:52 am
I did it with a Matrix report.
Row: ProductDate
Column: PersonName
Intersection (value): ProductName
or did it have to be in query?
July 12, 2013 at 10:32 am
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....
July 12, 2013 at 10:54 am
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[/url]
July 12, 2013 at 11:29 am
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....
July 12, 2013 at 11:39 am
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.
July 12, 2013 at 8:33 pm
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply