How do I transpose or pivot subgroups into to a single row?

Question

I have a group-by-top-n-results query that is shown in the example input data. The subgroups (grouped by ID) are limited to the top 10 results and they are sorted ASC by rank. How do I go from the input example to the output example?

I was thinking it might be some sort of pivot function or a crosstab solution, or maybe it needs to be joined on itself. I'm just not sure exactly how to make this work. It's almost as if we concatenate each subgroup on its own row.

Each subgroup can have a maximum of 10 top results, but may also not have the full 10. In the example, the subgroup 1003 & 1007 do not have results past the top 6 and past the top 3 respectively. The schema in the example output is what I am looking for with all fields of 10 possible top-ranked rows as columns.

Example input data


+------+------+----------------------+---------+---------+-------+---------+---------+
| rank |  ID  |         NAME         | FIELD 1 | FIELD 2 | MAIN  | FIELD 3 | FIELD 4 |
+------+------+----------------------+---------+---------+-------+---------+---------+
|    1 | 1001 | Backdash             |  123053 |       2 |  21.1 |   17.09 |      20 |
|    2 | 1001 | cold Stone Creamery  |  115404 |       2 | 19.78 |    1.04 |    0.93 |
|    3 | 1001 | Mado                 |   97650 |       2 | 16.74 |     0.1 |    0.14 |
|    4 | 1001 | Friendly's           |   57638 |       1 |  9.88 |    0.21 |     0.4 |
|    5 | 1001 | Fosters Freeze       |   53187 |       2 |  9.12 |    0.24 |       1 |
|    6 | 1001 | Marble Slab Creamery |   51381 |       2 |  8.81 |   15.75 |   28.57 |
|    7 | 1001 | Lappert's            |   35929 |       1 |  6.16 |    0.01 |    0.04 |
|    8 | 1001 | Greater's            |   23050 |       1 |  3.95 |    0.01 |    0.05 |
|    9 | 1001 | Happy Joe's          |   20422 |       1 |   3.5 |   12.73 |      25 |
|   10 | 1001 | Shake Shack          |    4260 |       1 |  0.73 |     8.1 |      50 |
|    1 | 1003 | Mauds Ice Cream      |  949152 |      11 | 22.29 |    0.98 |    0.75 |
|    2 | 1003 | Mr Whippy            |  433590 |       5 | 10.18 |    0.61 |    0.78 |
|    3 | 1003 | New Zeland Natural   |  411348 |       7 |  9.66 |    0.03 |    0.12 |
|    4 | 1003 | Tropical Sno         |  394558 |      10 |  9.27 |    0.15 |     0.4 |
|    5 | 1003 | Culver's             |  375755 |       5 |  8.82 |    3.47 |    2.96 |
|    6 | 1003 | Marble Slab Creamery |  276971 |       7 |   6.5 |   13.05 |   12.07 |
|    1 | 1007 | Greater's            |  105866 |       2 | 58.96 |   19.91 |    12.5 |
|    2 | 1007 | Hagan-Daz            |   37697 |       3 | 20.99 |   26.17 |    37.5 |
|    3 | 1007 | cold Stone Creamery  |   25520 |       1 | 14.21 |    0.23 |    0.47 |
+------+------+----------------------+---------+---------+-------+---------+---------+

Example Output Format


+------+-----------------+---------------+----------------+-------------+----------------+-----------------+---------------------+----------------+----------------+-------------+----------------+-----------------+---------------------+----------------+----------------+-------------+----------------+-----------------+--------------+----------------+----------------+-------------+----------------+-----------------+----------------+----------------+----------------+-------------+----------------+-----------------+----------------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+--------------+-----------------+-----------------+--------------+-----------------+------------------+
|  ID  |   TOP 1 NAME    | TOP 1 FIELD 1 |  TOP 1 FIELD 2 |  TOP 1 MAIN |  TOP 1 FIELD 3 |  TOP 1 FIELD 4  |      TOP 2 NAME     |  TOP 2 FIELD 1 |  TOP 2 FIELD 2 |  TOP 2 MAIN |  TOP 2 FIELD 3 |  TOP 2 FIELD 4  |      TOP 3 NAME     |  TOP 3 FIELD 1 |  TOP 3 FIELD 2 |  TOP 3 MAIN |  TOP 3 FIELD 3 |  TOP 3 FIELD 4  |  TOP 4 NAME  |  TOP 4 FIELD 1 |  TOP 4 FIELD 2 |  TOP 4 MAIN |  TOP 4 FIELD 3 |  TOP 4 FIELD 4  |   TOP 5 NAME   |  TOP 5 FIELD 1 |  TOP 5 FIELD 2 |  TOP 5 MAIN |  TOP 5 FIELD 3 |  TOP 5 FIELD 4  |      TOP 6 NAME      |  TOP 6 FIELD 1 |  TOP 6 FIELD 2 |  TOP 6 MAIN |  TOP 6 FIELD 3 |  TOP 6 FIELD 4  |  TOP 7 NAME |  TOP 7 FIELD 1 |  TOP 7 FIELD 2 |  TOP 7 MAIN |  TOP 7 FIELD 3 |  TOP 7 FIELD 4  |  TOP 8 NAME |  TOP 8 FIELD 1 |  TOP 8 FIELD 2 |  TOP 8 MAIN |  TOP 8 FIELD 3 |  TOP 8 FIELD 4  |  TOP 9 NAME |  TOP 9 FIELD 1 |  TOP 9 FIELD 2 |  TOP 9 MAIN |  TOP 9 FIELD 3 |  TOP 9 FIELD 4  |  TOP 10 NAME |  TOP 10 FIELD 1 |  TOP 10 FIELD 2 |  TOP 10 MAIN |  TOP 10 FIELD 3 |  TOP 10 FIELD 4  |
+------+-----------------+---------------+----------------+-------------+----------------+-----------------+---------------------+----------------+----------------+-------------+----------------+-----------------+---------------------+----------------+----------------+-------------+----------------+-----------------+--------------+----------------+----------------+-------------+----------------+-----------------+----------------+----------------+----------------+-------------+----------------+-----------------+----------------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+--------------+-----------------+-----------------+--------------+-----------------+------------------+
| 1001 | Backdash        |        123053 |              2 |        21.1 |          17.09 |              20 | cold Stone Creamery |         115404 |              2 |       19.78 |           1.04 |            0.93 | Mado                |          97650 |              2 |       16.74 |            0.1 |            0.14 | Friendly's   |          57638 |              1 |        9.88 |           0.21 |             0.4 | Fosters Freeze |          53187 |              2 |        9.12 |           0.24 |               1 | Marble Slab Creamery |          51381 |              2 |        8.81 |          15.75 |           28.57 | Lappert's   |          35929 |              1 |        6.16 |           0.01 |            0.04 | Greater's   |          23050 |              1 |        3.95 |           0.01 |            0.05 | Happy Joe's |          20422 |              1 |         3.5 |          12.73 |              25 | Shake Shack  |            4260 |               1 |         0.73 |             8.1 |               50 |
| 1003 | Mauds Ice Cream |        949152 |             11 |       22.29 |           0.98 |            0.75 | Mr Whippy           |         433590 |              5 |       10.18 |           0.61 |            0.78 | New Zeland Natural  |         411348 |              7 |        9.66 |           0.03 |            0.12 | Tropical Sno |         394558 |             10 |        9.27 |           0.15 |             0.4 | Culver's       |         375755 |              5 |        8.82 |           3.47 |            2.96 | Marble Slab Creamery |         276971 |              7 |         6.5 |          13.05 |           12.07 | -           |              - |              - |           - |              - |               - | -           |              - |              - |           - |              - |               - | -           |              - |              - |           - |              - |               - | -            |               - |               - |            - |               - |                - |
| 1007 | Greater's       |        105866 |              2 |       58.96 |          19.91 |            12.5 | Hagan-Daz           |          37697 |              3 |       20.99 |          26.17 |            37.5 | cold Stone Creamery |          25520 |              1 |       14.21 |           0.23 |            0.47 | -            |              - |              - |           - |              - |               - | -              |              - |              - |           - |              - |               - | -                    |              - |              - |           - |              - |               - | -           |              - |              - |           - |              - |               - | -           |              - |              - |           - |              - |               - | -           |              - |              - |           - |              - |               - | -            |               - |               - |            - |               - |                - |
+------+-----------------+---------------+----------------+-------------+----------------+-----------------+---------------------+----------------+----------------+-------------+----------------+-----------------+---------------------+----------------+----------------+-------------+----------------+-----------------+--------------+----------------+----------------+-------------+----------------+-----------------+----------------+----------------+----------------+-------------+----------------+-----------------+----------------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+-------------+----------------+----------------+-------------+----------------+-----------------+--------------+-----------------+-----------------+--------------+-----------------+------------------+

PS.

I am coming from a coding mindset, and haven't done SQL in a while. In sudocode this could be solved with a simple nested for-loop. Eg.

foreach subgroup i
    foreach rank j <= 10
        print i, rank[j].name, rank[j].FIELD 1, rank[j].FIELD 2, rank[j].MAIN, rank[j].FIELD 3, rank[j].FIELD 4
    print \r\n

score 1 · Accepted Answer · answered May 21 '19 at 11:52

1

You can use conditional aggregation:

select id,
       max(case when rank = 1 then name end) as name_1,
       max(case when rank = 1 then field1 end) as field1_1,
       . . .
       max(case when rank = 2 then name end) as name_2,
       max(case when rank = 2 then field1 end) as field1_2,
       . . .
       max(case when rank = 10 then field3 end) as field3_10,
       max(case when rank = 10 then field4 end) as field4_10
from inputdata id
group by id;

answered May 21 '19 at 11:52

Gordon Linoff

1,242,037
58
646
786

CONDITIONAL AGGREGATION! This looks like its working. I'm testing with my data right now. I'll check back in here soon. – italiansoda May 21 '19 at 15:10

How do I transpose or pivot subgroups into to a single row?

1 Answers1