August 13, 2007 at 1:45 pm
I want to select the values of a column and use those values as column headings create a result that looks like the last set of data below. I have no idea how to do this or if it is possible. Thanks in advance, vmon
tblPropertyId
PropertyId
Length
Width
Thickness
tblValue
PropertyId ValueId
Length 12
Length 24
Length 36
Width 2
Width 4
Width 6
Thickness 1
Thickness 2
Thickness 3
tblItemPropertyValue
ItemId
PropertyId
ValueId
A Length 12
A Width 2
A Thickness 1
B Length 12
B Width 2
B Thickness 2
Item Length Width Thickness
A 12 2 1
B 12 2 2
August 13, 2007 at 3:37 pm
You may try to use pivot feature :
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/55f5db6e-6777-435f-8a06-b68c129f8437.htm
August 16, 2007 at 5:06 pm
I don't know if you can use pivot without an aggregation function. You can self join the table.
I'm just guessing on the design of your schema, looks like some type of standard product with length width and height. You might be better off modeling the entities and their attributes instead of building what looks like it could become a series of EAV tables.
CREATE TABLE #tblItemPropertyValue
(ItemId CHAR(1),
PropertyId VARCHAR(10),
ValueId TINYINT)
INSERT INTO #tblItemPropertyValue (ItemId, PropertyId, ValueId) VALUES ('A','Length',12)
INSERT INTO #tblItemPropertyValue (ItemId, PropertyId, ValueId) VALUES ('A','Width',2)
INSERT INTO #tblItemPropertyValue (ItemId, PropertyId, ValueId) VALUES ('A','Thickness',1)
INSERT INTO #tblItemPropertyValue (ItemId, PropertyId, ValueId) VALUES ('B','Length',12)
INSERT INTO #tblItemPropertyValue (ItemId, PropertyId, ValueId) VALUES ('B','Width',2)
INSERT INTO #tblItemPropertyValue (ItemId, PropertyId, ValueId) VALUES ('B','Thickness',2)
SELECT Item.ItemID, l.ValueId AS Length, w.ValueId AS Width, t.ValueId AS Thickness
FROM
(SELECT ItemID
FROM #tblItemPropertyValue
GROUP BY ItemID) Item
INNER JOIN #tblItemPropertyValue l
ON item.ItemId = l.ItemId AND l.PropertyId = 'Length'
INNER JOIN #tblItemPropertyValue w
ON item.ItemId = w.ItemId AND w.PropertyId = 'Width'
INNER JOIN #tblItemPropertyValue t
ON item.ItemId = t.ItemId AND t.PropertyId = 'Thickness'
-- returns
ItemID Length Width Thickness
------ ------ ----- ---------
A 12 2 1
B 12 2 2
DROP TABLE #tblItemPropertyValue
August 17, 2007 at 2:13 am
SELECT [Length]
, [Width]
, [Thickness]
FROM ( SELECT ItemID
, PropertyID
, ValueID
FROM mytable
) AS SourceTable PIVOT ( AVG(ValueID)
FOR PropertyID
IN ( [Length], [Width], [Thickness] )
) AS PivotTable
This assumes that there is only one value for a particular property (otherwise it just takes the average)
Regards,
Andras
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply