0

Maybe this question has been asked already (it should be quite simple). I've Googled loads of phrases (PIVOT, ROWS to COLUMNS, TRANSPOSE &c.) and all I get is TSQL this, TSQL that and or Oracle this, Oracle that, proprietary function this, proprietary function that well, you get the picture.

I have a simple table structure - if it was any simpler, it'd be a list :-) - i.e. it's two columns.

stock_date       sku
----------       ---
2016-06-01         0
2016-06-02       123
2016-06-03       123
2016-06-04       123
2016-06-06       123
2016-06-07       123
2016-06-03       245
<... &c...>

There may be many thousands of sku's but the date range will be at most a month or so, but can vary, so the code needs to cater for that.

So, I want to make the stock_date field a column.

sku     1   2   3   4   5   6   7
---     -   -   -   -   -   -   - 
123     0   Y   Y   Y   0   Y   Y
245     0   0   Y.... &c.

Ideally, the code would be dynamic and would work with any server (even MySQL).

But if it has to use common table expressions and/or window functions, that's OK but not proprietary crosstab or pivot functions.

  • There's no standard for dynamic SQL. Every vendor implements their own solution to that. Partly the reason is that SQL proper is a declarative language. You aren't telling the server how to achieve the result, only what to achieve, but the what (e.g. how many columns and what they should be called) should be stated explicitly. So, in order to be vague about the result ("hey server, figure out the number and names of the columns yourself"), you build a query dynamically and that's where you cease being declarative. The non-declarative part of SQL is platform-specific, no way around that. – Andriy M Jul 29 '16 at 8:21
  • As for platform-independent method of pivoting, conditional aggregation may be what you are looking for. – Andriy M Jul 29 '16 at 8:22
1

A PIVOT can be written as a CASE statement. So for your example:

SELECT
    sku,
    MAX (CASE WHEN stock_date = '2016-06-01' THEN 'Y' ELSE 'N' END) AS [1],
    MAX (CASE WHEN stock_date = '2016-06-02' THEN 'Y' ELSE 'N' END) AS [2],
    MAX (CASE WHEN stock_date = '2016-06-03' THEN 'Y' ELSE 'N' END) AS [3],
    ...
FROM
    dbo.mytable
GROUP BY sku

Note that this can (and should) be dynamically generated.

(Source: https://avaldes.com/pivot_using_case/)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy