Select data as columns

  • 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

  • You may try to use pivot feature :

    ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/extran9/html/55f5db6e-6777-435f-8a06-b68c129f8437.htm

  • 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

  • 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


    Andras Belokosztolszki, MCPD, PhD
    GoldenGate Software

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply