CODEWITHSUNDEEP
sql-server
MartinHN
MartinHN

Reputation: 19772

SQL make rows into columns, PIVOT maybe

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?

Upvotes: 0

Views: 12666

Answers (3)

JBrooks
JBrooks

Reputation: 10013

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.

Upvotes: 1

James Black
James Black

Reputation: 41858

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.

Upvotes: 0

mschmidt42
mschmidt42

Reputation: 1095

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.

Upvotes: 0

Related Questions