SQL make rows into columns, PIVOT maybe

Question

Hi

I have an MS SQL Server with a database for an E-commerce storefront.

This is some of the tables I have:

Products:  
Id | Name | Price

ProductAttributeTypes: -Color, Size, Format  
Id | Name

ProductAttributes: --Red, Green, 12x20 cm, Mirrored  
Id | ProductAttributeTypeId | Name

Orders:  
Id | DateCreated

OrderItems:  
Id | OrderId | ProductId

OrderItemsToProductAttributes: --Relates an OrderItem to its product and selected attributes  
OrderItemId | ProductAttributeId | ProductAttributeTypeId | ProductId

I want to select from the OrderItems table, to see which items have been purchased.

To see what kind of variants (ProductAtriibutes) was selected, I want those as "dynamic" columns in the resultset.

So the resultset should look like this:

OrderItemId | ProductId | ProductName | Color | Size | Format  
       1234         123   Mount. Bike   Red     2x20   Mirror

I don't know if PIVOT is the thing to use? I'm not using any aggregate functions, so I guess not...

Is there any SQL Ninjas that can help me out?

Answer 1

In the past, I've created physical tables for read purposes only. The structure you have above is GREAT for storage, but terrible for reporting.

So you could do the following: Write a script (that is scheduled nightly) or a trigger (on data change) that does the following tasks:

First, you would dynamically go through each Product and build a static table "Product_[ProductName]"

Then go through each ProductAttributeTypes for each product and create/update/delete a physical column on the corresponding Product table.

Then, fill that table with the proper values based on OrderItemsToProductAttributes and ProductAttributes

This is just a rough idea. Make sure you are storing OrderID in the "Static"/"Flattened" tables. And make sure you do everything else you need to do. But after that, you should be able to start pulling from those flattened tables to get the data you need.

Answer 2

If you are using sql2005 or 2008 you can use the pivot command. See here.

In the example below the OrderAttributes set will look like:

 OrderItemId AttName AttValue
       -----  ------  -----
        100 Color Red
        100 Size Small
        101 Color Blue
        101 Size Small
        102 Color Red
        102 Size Small
        103 Color Blue
        103 Size Large

The final results after the PIVOT will be:

OrderItemId Size  Color
  -----    ------  -----
    100 Small Red
    101 Small Blue
    102 Small Red
    103 Large Blue

WITH OrderAttributes(OrderItemId, AttName, AttValue)
   AS (
      SELECT 
         OrderItemId, 
         pat.Name AS AttName,
         pa.Name AS AttValue
      FROM OrderItemsToProductAttributes x
      INNER JOIN ProductAttributes pa 
      ON x.ProductAttributeId = pa.id
      INNER JOIN ProductAttributeTypes pat
      ON pa.ProductAttributeTypeId =  pat.Id
   )

SELECT AttrPivot.OrderItemId,
[Size] AS [Size],
[Color] AS Color
FROM OrderAttributes
PIVOT ( 
      MAX([AttValue])
      FOR [AttName] IN ([Color],[Size])
   ) AS AttrPivot
ORDER BY AttrPivot.OrderItemId

There is a way to dynamically build the columns (i.e. the Color and Size columns), as can be seen here. Make sure your database compatibility level on your database is set to something greater than 2000 or you will get strange errors.

Answer 3

Pivot is your best bet, but what I did for reporting purposes, and to make it work well with SSIS is to create a view, which then has this query:

SELECT     [InputSetID], [InputSetName], CAST([470] AS int) AS [Created By], CAST([480] AS datetime) AS [Created], CAST([479] AS int) AS [Updated By], CAST([460] AS datetime) 
                      AS [Updated]
FROM         (SELECT     st.InputSetID, st.InputSetName, avt.InputSetID AS avtID, avt.AttributeID, avt.Value
                       FROM          app.InputSetAttributeValue avt JOIN
                                              app.InputSets st ON avt.InputSetID = st.InputSetID) AS p PIVOT (MAX(Value) FOR AttributeID IN ([470], [480], [479], [460])) AS pvt

Then I can just interact with the view, but, I have a trigger on the table that any new dynamic attributes must be added to, which recreates this view, so I can assume the view is always correct.