Query with dynamic columns and a count on each column

Question

Forgive me if this has been asked as I didn't even know what to search on.

I have two tables, SKU, and OrderSKU, that look like this:

SKU_Code      Description
-----------------------------
1001          Product1
1002          Product2
1003          Product3
1004          Product5

OrderID       SKU     Quantity
-----------------------------
13            1001    34
13            1002    15
13            1004    2
15            1003    7
15            1004    8

I'd like a query that selects all the rows in the SKU table and then gives me a count for each sku in the OrderSku table (even if zero), grouped by Order ID. Ideally, the resultset would look like this:

OrderID   1001  1002   1003   1004
-------------------------------------
13        34    15     0      2
15        0     0      7      8

Is this possible?

Thanks, jeff

Stephan · Accepted Answer · 2015-05-05 18:37:47Z

3

What you are looking for is dynamic SQL since SQL Server doesn't allow inputting a list of values into the pivot function directly. Try this out:

DECLARE @SelectCols VARCHAR(MAX),
        @PivotCols  VARCHAR(MAX);

SELECT  @SelectCols = COALESCE(@SelectCols + ',','') + 'ISNULL(' + QUOTENAME(Sku) + ',0) AS ' + QUOTENAME(sku),
        @PivotCols  = COALESCE(@PivotCols + ',','') + QUOTENAME(Sku)
FROM OrderSku
GROUP BY Sku

SELECT @SelectCols,@PivotCols

EXEC
(
    'SELECT DISTINCT OrderID,' + @SelectCols +
    ' FROM OrderSku 
    INNER JOIN [Order] SO
    ON      orderid = SO.salesorderid
        AND SO.orderstatusid IN (1,2,3)
    PIVOT
    (
        MAX(Quantity) FOR SKU IN (' + @PivotCols + ')
    ) pvt'
)

edited May 5, 2015 at 18:37

answered May 5, 2015 at 16:40

Stephan

6,0581 gold badge19 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

JeffO Over a year ago

do i need to hardcode the sku-codes in both parts of the query? or, more accurately, is there a way i can do this without hardcoding and using a query on the SKU table?

JeffO Over a year ago

Thank you. If i wanted to join a table in the second query to add a "Where" clause like this, 'inner join order so on ordersku.orderid = so.salesorderid where so.orderstatusid in (1,2,3)'; should that cause an issue?

Stephan Over a year ago

Well what will happen with an inner join is that if you there are multiple matches, then you will have duplicates. Try using cross apply instead like in my edited code.

JeffO Over a year ago

Just tried that and it gave me a row for every separate order/sku combination (in my original example, there would have been three rows returned for order id 13 and two rows returned for orderid 15). also, the where clause didn't seem to work cause it returned all order ids, regardless of OrderStatusID.

Stephan Over a year ago

Interesting... Try my new edited code. If it doesn't work, some more sample data would help

Community · Accepted Answer · 2017-03-20 10:04:24Z

1

Exactly what you want is only possible if you know what SKU's you want in advance, or if you're willing to use dynamic sql. If you have a lot of SKUs, it's probably not feasible to do this at the database server level, and you'll want to do the PIVOT in client code.

If you do opt to pivot on the client, then you want something like this:

With SkuOrderCross As 
(
   SELECT DISTINCT SKU.SKU_Code, OrderID
   FROM OrderSku
   INNER JOIN SalesOrder so ON so.SalesOrderID = OrderSku.OrderID
   CROSS JOIN SKU
enter code here
   WHERE so.OrderStatusID in (1,2,3)
)
SELECT c.OrderID, c.Sku_Code, coalesce(o.Quantity, 0) Quantity
FROM SkuOrderCross c
LEFT JOIN OrderSku o ON o.OrderID = c.OrderID AND o.SKU = c.Sku_Code
ORDER BY c.OrderID, c.Sku_Code

That query will provide the raw data that you want for the pivot.

If this is just for one-time or infrequent report, the following SuperUser question demonstrates how to convert the data retrieved from this query into your desired format after you load it into Excel:

https://superuser.com/questions/569185/convert-a-table-of-3-columns-of-x-y-value-to-the-matrix-table

edited Mar 20, 2017 at 10:04

CommunityBot

11 silver badge

answered May 5, 2015 at 16:50

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

4 Comments

JeffO Over a year ago

I'm trying to add a "where" clause and inserted the following into your code: left join salesorder so on o.OrderID = so.SalesOrderID where so.OrderStatusID in (1,2,3) this caused only rows with quantities that are not NULL to be returned. any help on how to filter this query by the status linked in the SalesOrder table?

Joel Coehoorn Over a year ago

Add the where clause to the cross join

JeffO Over a year ago

How would that look, syntactically?

Joel Coehoorn Over a year ago

I edited into the question, but reading again, you could also add this as an INNER JOIN to the later query.

Collectives™ on Stack Overflow

Query with dynamic columns and a count on each column

2 Answers 2

5 Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related