July 9, 2010 at 11:09 am
/* I've been looking in vain for a while now for a solution to the problem of turning rows into columns. My problems is kind of like this:
*/
CREATE TABLE product (ProductID INT , ProductName VARCHAR(255))
INSERT INTO product values (1,'First Product')
go
CREATE TABLE product_items (productID INT , ItemID INT )
INSERT INTO product_items values (1, 10)
INSERT INTO product_items values (1, 20)
INSERT INTO product_items values (1, 30)
INSERT INTO product_items values (1, 40)
go
/* the query: */
SELECT * FROM product_items WHERE productID = 1
/* yields this:
productIDitemID
110
120
130
140
but what i want is this:
ProductIDitem1item2item3item4
110203040
What i have come up with so far is the following:
*/
SELECT p.productID
, (SELECT TOP 1 itemid FROM product_items WHERE productID = p.productID) AS item1
, (SELECT TOP 1 itemid FROM product_items WHERE productID = p.productID AND itemid NOT IN (SELECT TOP 1 itemid FROM product_items WHERE productID = p.productID)) AS item2
, (SELECT TOP 1 itemid FROM product_items WHERE productID = p.productID AND itemid NOT IN (SELECT TOP 2 itemid FROM product_items WHERE productID = p.productID)) AS item3
, (SELECT TOP 1 itemid FROM product_items WHERE productID = p.productID AND itemid NOT IN (SELECT TOP 3 itemid FROM product_items WHERE productID = p.productID)) AS item4
FROM products p
WHERE p.productID = 1
/*... but that is really ugly especially when i need to return a lot more that 4 items (more like 30)
isn't there an easier/better way to do this? ideally my solution would work for a variable number of itemIDs, but i'd settle for a fixed number too.
i've done a lot of looking, but have found nothing on how to get the results i need. Which seems strange to me
since all i really want is a denormalized view of my data, kind of like the classic example of normalization of
phone numbers: if we have a table like:
custPhone( CustomerID, Phone1, Phone2, Phone3)
we are told to normalize to something like (CustID, PhoneID), with a variable number of phone numbers stored in another table.
isn't there a way to get back to a view of the original, non-normal table?
Any ideas are greatly appreciated.
-Drew
*/
DROP TABLE product
DROP TABLE product_items
July 9, 2010 at 11:24 am
oh, i forgot to mention: for you convenience, the whole original post is written so that you can cut and paste the whole thing into your query analyzer window and run. but you probably noticed that.
July 9, 2010 at 11:36 am
Only 4 columns are possible? i mean, for a given ID, will there be only 4 columns ?
July 9, 2010 at 11:38 am
July 9, 2010 at 11:38 am
great job on providing the data we need to test.
rows to columns and columns to rows is probably the single most asked question on the forum; i bet there are two or three every day.
the method you are looking for is PIVOT;
pivot can move rows to columns ofr you, but you need to know how many values you are expecting....from your example, i'm thinking 4 columns. the advantage of this technique below, which uses rownumber(), is that it can be quickly adapted to more or less columns quickly:
here is a working exampel that does what you asked:
--results:
productID 1 2 3 4
1 40 30 20 10
--------------------
CREATE TABLE product (ProductID INT , ProductName VARCHAR(255))
INSERT INTO product values (1,'First Product')
go
CREATE TABLE product_items (productID INT , ItemID INT )
INSERT INTO product_items values (1, 10)
INSERT INTO product_items values (1, 20)
INSERT INTO product_items values (1, 30)
INSERT INTO product_items values (1, 40)
go
;with cte as
(
select productID ,ItemID,
row_number() over (partition by productID order by ItemID desc) RN
from product_items
)
select * from
( select * from cte where RN <= 4 ) pivot_handle
pivot
(MAX(ItemID) for RN in ([1],[2],[3],[4])) pivot_table
Lowell
July 9, 2010 at 11:59 am
Thanks for all the replies so far, things are looking up!
coldcoffee: ideally i the query would have a variable number of items, but i'll take a fixed in variable is not possible. My real world problem is much more complicated than the example i created. i just chose 4 items to be simple about things.
Lowell: Thanks dude. i looked into the Pivot operator, but every example i came across involved summing data and involved a third column for grouping. i'm looking at your example now. thanks again.
July 9, 2010 at 12:18 pm
this works nicely. i am still trying to figure out just how that final part works:
pivot
(MAX(ItemID) for RN in ([1],[2],[3],[4])) pivot_table
as it seems the row number will always be the column name.
Any ideas on how to customize the column names? i'll need to have something like Item1, Item2... or more likely Item1Name, Item2Name, Item1sku, item2 sku.
July 9, 2010 at 12:26 pm
here's an example, but i don't think you can generate then column names dynamically this way(i know you will ask that!)..i think the AS 'Val' + RN will not work.
;with cte as
(
select productID ,ItemID,
row_number() over (partition by productID order by ItemID desc) RN
from product_items
)
select productID,[1] as Val1,[2] as Val2,[3] as Val3,[4] as Val4 from
( select * from cte where RN <= 4 ) pivot_handle
pivot
(MAX(ItemID) for RN in ([1],[2],[3],[4])) pivot_table
Lowell
July 9, 2010 at 8:18 pm
bagofbirds-767347 (7/9/2010)
ideally i the query would have a variable number of items
In that case, in addition to reading Part 1 of the article that CC mentioned above, check out Part 2. You can find the link in my signature below. It deals with dynamic pivot, which is what you need for a variable number of items.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
July 10, 2010 at 12:17 am
oops..
Lowell
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply