Stunna Stunna - 1 year ago 192
SQL Question

SQL: Convert a Narrow Table to a Wide Table

I have a question that is somewhat similar to this question already asked: Mysql, reshape data from long / tall to wide

My complexity comes with the fact that my data structure can be volatile and is subject to change at the user's discretion. So my columns can change aggregation (grow or shrink) depending upon the fields that populate the table.

For an example of what might be in my table, here are some records:


LOCATION User_Name Title Phone
Living Room Joe Schmo Worker 12-23
Baseball Park Jane Doe Worker 23-34
Backyard Tiger Woods Worker 34-45

but there's a fairly good amount of variability in the Title, and maybe even possibly in Location


LOCATION User_Name Title Phone
Living Room Batman Manager 9112
Baseball Park Batman Manager 9112
Backyard Batman Manager 9112

So what I need to do, on matched Locations, I need to have a wide table with all users associated to Location:


LOCATION User_Name Title Phone User_Name Title Phone
Living Room Joe Schmo Worker 12-23 Batman Manager 9112
Baseball Park Jane Doe Worker 23-34 Batman Manager 9112
Backyard Tiger Woods Worker 34-45 Batman Manager 9112

Any suggestions tips would be highly appreciated. I tried to replicate the example, but obviously how do I account for the fact that table can grow wider or narrow dynamically? I tried to do pivots but I couldn't get it to work with multiple fields.


Answer Source

You can do it using PIVOT query.
You need to decide in advance how wide a result should be - I mean how many "groups" of columns (name,title,phone) a final result should have.

The below example divides the whole table into 3 groups of (name+title+phone) columns:

WITH my_data AS (
    SELECT LOCATION, User_Name, Title, Phone,
           trunc( rn / 3 ) as group_number,
           rn - 3 * trunc( rn / 3 ) as rownum_within_group
    FROM (
        SELECT t.* ,
              row_number() over (partition by location order by user_name ) - 1 As rn
        FROM table1 t
    ) t
    -- ORDER BY Location, user_name
FROM my_data
   MAX( User_name) As user_name,
   MAX( Title ) as Title,
   MAX( Phone ) As Phone
   FOR rownum_within_group IN (0 as G0,1 As G1 ,2 As G2)

and a sample result for slightly modified data from your example (I've added additional "Batman2"+"Batman3"+"Batman4" entries) is:

Backyard                 0  Batman          Manager     9112        Tiger Woods     Worker      34-45           
Baseball Park            0  Batman          Manager     9112        Jane Doe        Worker      23-34           
Living Room              0  Batman          Manager     9112        Batman2         Manager     9112        Batman3         Manager     9112
Living Room              1  Batman4         Manager     9112        Joe Schmo       Worker      12-23   

There are 5 users in location Living Room, so this location is divided into two rows in the results set, the first row has 3 users, and the second row has 2 users (see two last rows in the example above).