Stunna Stunna - 5 months ago 41
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:

INPUT:

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

INPUT:

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:

OUTPUT:

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.

Thoughts?

Answer

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
)
SELECT * 
FROM my_data
PIVOT (
   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)
)
ORDER BY 1,2;

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

LOCATION       GROUP_NUMBER G0_USER_NAME    G0_TITLE    G0_PHONE    G1_USER_NAME    G1_TITLE    G1_PHONE    G2_USER_NAME    G2_TITLE    G2_PHONE
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).