CODEWITHSUNDEEP
sqlselectpivot
Milnelli
Milnelli

Reputation: 55

SQL pivot with text based fields

Forgive me, but I can't get this working. I can find lots of complex pivots using numeric values, but nothing basic based on strings to build upon.

Lets suppose this is my source query from a temp table. I can't change this: 

select * from @tmpTable

This provides 12 rows:

Row | Name           | Code
---------------------------------
1   | July 2019      | 19/20-01
2   | August 2019    | 19/20-02
3   | September 2019 | 19/20-03 
..  ..                 ..
12  | June 2020      | 19/20-12

I want to pivot this and return the data like this:

    Data Type  |      [0]     |      [1]      |       [3]        |  [12]
---------------------------------------------------------------------------
    Name       |   July 2019  |  August 2019  | September 2019   |  June 2020
    Code       |   19/20-01   |  19/20-02     | 19/20-03         |  19/20-12

Thanks in advance..

Upvotes: 0

Views: 164

Answers (1)

Caius Jard
Caius Jard

Reputation: 74605

Strings and numbers aren't much different in pivot terms, it's just that you can't use numeric aggregators like SUM or AVG on them. MAX will be fine and in this case you'll only have one Value so nothing will be lost

You need to pull your data out to a taller key/value representation before pivoting it back to look the other way round as it does now

unpivot the data:

WITH upiv AS(
   SELECT 'Name' as t, row as r, name as v FROM @tempTable
   UNION ALL
   SELECT 'Code' as t, row, code FROM @tempTable
)

Now the data can be re grouped and conditionally aggregated on the r columns:

SELECT
  t,
  MAX(CASE WHEN r = 1 THEN v END) as r1,
  MAX(CASE WHEN r = 2 THEN v END) as r2,
  ...
  MAX(CASE WHEN r = 12 THEN v END) as r12
FROM
  upiv
GROUP BY
  t

You'll need to put the two sql blocks I present here together so they form a single sql statement. If you want to know more about how this works, I suggest you run the sql statement inside the with block, take a look at it, and also remove the group by/max words from the full statement and look at the result. You'll see the WITH block query makes the data taller, essentially a key/value pair that is tracking what type the data is (name or code). When you run the full sql without the group by/max you'll see the tall data spreads out sideways to give a lot of nulls and a diagonal set of cell data (if ordered by r). The group by collapses all these nulls because a MAX will pick any value over null (of which there is only one)

You could also do this as an UNPIVOT followed by a PIVOT. I've always preferred to use this form because not every database supports the UN/PIVOT keywords. Arguably, UNPIVOT/PIVOT could perform better because there may be specific optimizations the developers can make (eg UNPIVOT could single scan a table; this multiple Union approach may require multiple scans and ways round it could be more memory intensive) but in this case it's only 12 rows. I suspect you're using SQLServer but if you're using a database that doesn't understand WITH you can place the bracketed statement of the WITH (including the brackets) between the FROM and the upiv to make it a subquery if the pattern SELECT ... FROM (SELECT ... UNION ALL SELECT ...) upiv GROUP BY ...; there is no difference

I'll leave renaming the output columns as an exercise for you but I would urge you to consider not putting spaces or square brackets in the column names as you show in your question

Upvotes: 1

Related Questions