SQL: Convert a Narrow Table to a Wide Table

Question

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?

Please reconsider your idea - your table is currently **well normalized**, What you want is to denormalize the table. Think a while - what if you want to get a simple answer `give me all users with a tile = 'Manager`. Currently is is very easy, just `SELECT * FROM table WHERE title='Manager'` - simple and very efficient, if an index is present on `Title`column. After the denormalization it must be: `SELECT * FROM table WHERE title1='Manager' OR title2='Manager' OR ...... OR Ttile2876 = 'Manager'`. The query is awkward, and the speed is veeeery poor. I higly recommend you to abondon this idea. — krokodilko, Jun 23 '16 at 20:15
Oh trust me, Trying to do this is like nails to a chalkboard. Unfortunately it's a requirement put on to me to denormalize this table for a presentation. That presentation doesn't have an abstraction layer for me to force them to denormalize it for me. So instead, the requirement is passed down to the database level to perform. TLDR; I would like to abandon this but I simply cannot. — Stunna, Jun 23 '16 at 20:44
Is the requirement to change the table structure in your database, or simply to generate OUTPUT that looks like what you requested? Normality refers only to the DB, not to reports you create. To your question: if this is a reporting requirement, it is usually best addressed through reporting applications rather than through straight SQL. — , Jun 23 '16 at 22:52
I have to change the structure in the database. The COTS product is a mapping product that requires strict parameters as an input and I don't have the option to write a service to do this lifting for me. It has to be on the database. — Stunna, Jun 24 '16 at 14:13

score 1 · Accepted Answer · edited Jun 24 '16 at 15:36

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

This definitely works and I'm amazed at how you got it to work so quickly. One thing i was wondering was how to not have a finite number of columns, or groups. But I don't think there's a way to avoid that because of the necessary aggregate function required in the pivot table? — Stunna, Jun 24 '16 at 14:53

SQL: Convert a Narrow Table to a Wide Table

1 Answers1