November 9, 2009 at 11:06 am
I have a Product table that desscibes all the attributes of a product. The attributes comes from the Attributes table not shown here. When ever a new product is added we list all the new attributes belonging to that new product in the Attributes table - which means only the new attributes which alrerady don't exist in the Product attribute table go in it.
Here is my Product table:
PROD_IDProd_AttributeProd_Desc
------------------------------
123ColorWHITE
123MemberId3245
123Number74232566
123Typeterminal
456ColorWhite
456Number98766332
456Pin99999999
456TypeCallCard
Desired Output: I would like to transpose the above table to look like below. My Prod_Attribute data from Products table becomes columns.
PROD_IDCOLORMemberIdTypeNumberPin
---------------------------------
123Blue3245Terminal74232566NULL
456White98766332CallCardNULL99999999
Note1: The attributes are not fixed...if today we have 10 attributes, tomorrow when a new product is introduced we might add 2 new attrubutes that don't already exist.
Note2: I tried using PIVOT but the problem with PIVOT is I have to use aggregate, where as I don't have any column to aggregate on. Please let me know if this can still be accomplished through PIVOT? OR any other solution is fine as well. Please help!!
Also, is there a better way to model a Product Catalog where new products are added frequently where inturn attributes get added frequiently. We could have modeled the Product table as the desired output above, but then we would have to add extra column every time a new attribute for the new product needs to be added. We didn't want to change the table structure by adding columns every time a new product is introduced, but rather keep the attributes at the data level.
All help is very greatly appreciated. Thanks in advance for all the help provided 🙂
November 9, 2009 at 3:50 pm
Here's code that does it dynamically so you don't have to change the code when you add attributes. The only thing I don't do is provide a set order of attributes:
IF OBJECT_ID(N'temp_prod', N'U') IS NOT NULL
BEGIN
DROP TABLE temp_prod
END
CREATE TABLE temp_prod
(
prod_id INT,
attrib VARCHAR(15),
prod_desc VARCHAR(25)
) ;
DECLARE @sql1 NVARCHAR(MAX),
@sql2 NVARCHAR(MAX),
@sql3 NVARCHAR(MAX),
@sql_exec NVARCHAR(MAX)
INSERT INTO
temp_prod
(
prod_id,
attrib,
prod_desc
)
SELECT
123,
'Color',
'Blue'
UNION ALL
SELECT
123,
'MemberId',
'3245'
UNION ALL
SELECT
123,
'Number',
'74232566'
UNION ALL
SELECT
123,
'TYPE',
'terminal'
UNION ALL
SELECT
456,
'Color',
'White'
UNION ALL
SELECT
456,
'Number',
'98766332'
UNION ALL
SELECT
456,
'Pin',
'99999999'
UNION ALL
SELECT
456,
'TYPE',
'CallCard'
SET @sql1 = N'Select prod_id, '
SELECT
@sql2 = COALESCE(@sql2, N'') + N'Min(Case when ' + QUOTENAME(attrib, '''') + N'= attrib then prod_desc else Null end) as ' + attrib + N',' + NCHAR(10)
FROM
(
SELECT DISTINCT
attrib
From
temp_prod
) AS a
SET @sql3 = N' from
(
Select
prod_id,
attrib,
min(prod_desc) as prod_desc
From
temp_prod
group by prod_id, attrib
) as A Group By prod_id '
SET @sql_exec = @sql1 + substring(@sql2, 1, LEN(@sql2)-2) + @sql3
EXEC(@sql_exec)
This is based on the techniques presented by Jeff Moden in the bottom 2 articles linked in my signature.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2009 at 4:02 pm
Found solution myself. I also looked at some other forums and it gave ideas and ideas and ideas and...long story short, below is the solution to dynamic transpose using PIVOT. I thought PIVOT is meant to aggregate only values, I was mistaken...I am using MAX function and it works...
If some one has a better solution please let me know or at least have it here so other folks like myself can benifit too. Again thanks in advance for the help.
Below Solution#1 and Solution#2 both spit out the same Desired Output as mentioned in my post above.
Solution #1...Not Dynamic - Col's are fixed.
SELECT ProdID, [Color], [memberid], [number], [Pin], [type]
FROM
(
SELECT ProdID, Chrs, ChrsDetails
FROM Prod
) AS source
PIVOT
(
MAX(ChrsDetails)
FOR Chrs IN ([Color], [memberid], [number], [Pin], [type])
) as pvt
Solution #2...Dynamic - Col's are not fixed...you can have any number of col's.
DECLARE @cols NVARCHAR(MAX)
SELECT @cols = COALESCE(@cols + ',' + QUOTENAME(p.CHRS), QUOTENAME(p.CHRS))
FROM (SELECT DISTINCT CHRS FROM PROD) p
ORDER BY p.CHRS
--PRINT @cols
DECLARE @query NVARCHAR(MAX)
SET @query = N'
SELECT ProdID, ' + @cols + ' --[Color], [memberid], [number], [Pin], [type]
FROM
(
SELECT ProdID, Chrs, ChrsDetails
FROM Prod
) AS source
PIVOT
(
MAX(ChrsDetails)
FOR Chrs IN (' + @cols + ') --([Color], [memberid], [number], [Pin], [type])
) as pvt'
EXECUTE(@query)
November 9, 2009 at 4:08 pm
Jack - thanks for the help:) I like your aproach too. But for now, I will stick to PIVOT as I already got the solution. Thanks again!
November 9, 2009 at 4:14 pm
No problem, as long as what you have works.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 9, 2009 at 9:59 pm
Thank you BOTH for posting your solutions. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply