SQL pivot/transpose/row-column non-proprietary

pivot

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.

Best Answer

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/)

Related Solutions

Sql-server – Pivot column query

The following will work with some assumptions:

statusreciept will be 0 or 1- if other values are possible and you want EACH prior location concatenated in the "Prior location" field, that is also doable, but requires a more robust solution.

that createddatetime is unique for all sets you want to track

     ;WITH    ITR
                  AS ( SELECT   CREATEDDATETIME ,
                                CREATEDBY ,
                                INVENTID ,
                                ITO.REFERENCECATEGORY ,
                                IT.STATUSRECEIPT
                       FROM     dbo.TRANS IT
                                INNER JOIN TRANSORIGIN ITO ON IT.TRANSORIGIN = ITO.RECID
                                                                    AND ITO.REFCATEGORY IN (
                                                                    2, 6 )
                       WHERE    CREATEDDATETIME > DATEADD(MONTH, -1, GETDATE())
                     ),
              DS AS
    (
            SELECT  ITR.CREATEDDATETIME ,
                    ITR.CREATEDBY AS [User] ,
                    YPT.PALLETNUMBER AS PalletNumber ,
                    ID.LOCATIONID AS [Current Location] ,
                    ID.PALLETID AS Pallet ,
                    WOT.DEPOTNAME AS Depot ,
                    ITR.REFERENCECATEGORY ,
                    ITR.STATUSRECEIPT

            FROM    dbo.PALLETTABLE AS YPT
                    INNER JOIN dbo.PALLET AS WP ON YPT.PALLETID = WP.PALLETID
                    LEFT OUTER JOIN dbo.DIM AS ID ON WP.PALLETID = ID.PALLETID
                                                           AND ID.BATCHID <> ''
                    LEFT OUTER JOIN dbo.ORDER AS WOT ON ID.DIMID = WOT.DIMID
                    LEFT OUTER JOIN ITR ON ID.DIMID = ITR.DIMID
                    ORDER BY WP.PALLETNUMBER),
   msr AS
(   SELECT createddatetime, max(statusreciept) statusreciept
FROM ds
GROUP BY createddatetime)



SELECT ds.createddatetime,
       ds.user,
       ds.palletnumber,
       ds.[current location],
       ds2.[current location] as [Previous Location],
       ds.pallet,
       ds.depot,
       ds.reference_category,
       ds.statusreciept AS statusreciept
FROM ds
LEFT JOIN ds ds2 
   ON ds2.createddatetime = ds.createddatetime
   AND ds2.statusreciept = ds.statusreciept - 1
INNER JOIN msr 
   ON msr.createddatetime = ds.createddatetime
   AND msr.statusreciept = ds.statusreciept
GROUP BY ds.createddatetime,
       ds.user,
       ds.palletnumber,
       ds.[current location],
       ds2.[current location],
       ds.pallet,
       ds.depot,
       ds.reference_category

Please let me know if either of the assumptions are incorrect or you need a different implementation.

Mysql – Pivot query issue while converting row to column

It is obvious, by how you have arranged the rows in your example, that there are two entities in the second table, each with its own set of attribute values.

However, in SQL it is a convention that rows in a table have no inherent order. Therefore, if you want the server to distinguish between the two sets, you need either

a column that would specify the row order (so that by using that order you could somehow determine where a new set of values starts):

category | fieldname | fieldvalue       | roworder
--------------------------------------------------
1        |     1     | SSC              | 1
1        |     2     | H.B.Kapadiya     | 2
1        |     3     | Primary school   | 3
1        |     4     | Mar-2000         | 4
1        |     5     | 89%              | 5
1        |     1     | HSC              | 6
1        |     2     | Vishwaniketan    | 7
1        |     3     | Higher Secondary | 8
1        |     4     | Mar-2002         | 9
1        |     5     | 70%              | 10

a column that would serve as an entity identifier, so that it would be clear which set of values belongs to which entity (and, thus, on which row in the output it should end up):

category | fieldname | fieldvalue       | entityid
--------------------------------------------------
1        |     1     | SSC              | 1
1        |     2     | H.B.Kapadiya     | 1
1        |     3     | Primary school   | 1
1        |     4     | Mar-2000         | 1
1        |     5     | 89%              | 1
1        |     1     | HSC              | 2
1        |     2     | Vishwaniketan    | 2
1        |     3     | Higher Secondary | 2
1        |     4     | Mar-2002         | 2
1        |     5     | 70%              | 2

The first option is certainly much inferior, because in order to get the desired result you would need to obtain some kind of entity identifier one way or another, and with the first option you would have to derive it somehow based on the order of rows. Note that you would probably be restricted to always storing the values in a specific order, and in particular it would be mandatory that values belonging to the same entity be stored in consecutive rows only.

With the second option you could store the values arbitrarily: the dedicated entity ID column would unambiguously determine which set of values the row should belong. Your query would then be very similar to what you already have, you would only need to add entityid to the GROUP BY:

SELECT
  b.Category, 
  MAX(IF(f.fieldname = 'educationlevel',   b.fieldvalue, NULL)) AS educationlevel,
  MAX(IF(f.fieldname = 'institution_name', b.fieldvalue, NULL)) AS institution_name,
  MAX(IF(f.fieldname = 'course',           b.fieldvalue, NULL)) AS course,
  MAX(IF(f.fieldname = 'year_of_passing',  b.fieldvalue, NULL)) AS year_of_passing,
  MAX(IF(f.fieldname = 'percentage',       b.fieldvalue, NULL)) AS percentage
FROM
  tbl_bulkuploads AS b
  INNER JOIN tbl_bulkuploadfields AS f on f.fid = b.fieldname
WHERE
  b.Category = 1
GROUP BY
  b.Category,
  b.entityid
;