SQL Query on Pivotted Data

xrapidx

xrapidx

Honorary Master
Joined
Feb 16, 2007
Messages
42,188
Reaction score
4,038
Location
Cape Town
Ok, this is annoying me, so I thought I'd try here, since a few of you might have some spare time,

I wrote a SQL Query to pivot a table using CASE statements,

I now have a table that looks like this
FIRST NAME | MIDDLE NAME | LAST NAME | OPTION 1 | OPTION 2 | OPTION 3 (all the way to 285, but this changes)
Adriaan | Jack | Egg | Yes | null | null
Adriaan | Jack | Egg | null | 34 | null
Adriaan | Jack | Egg | null | null | x
Joe | | Bob | No | null | null
Joe | | Bob | null | 31 | null
Joe | | Bob | null | 34 | null
Joe | | Bob | null | 36 | null
Joe | | Bob | null | 37 | null
Joe | | Bob | null | 11 | null
Joe | | Bob | null | null | yx


How do I collapse the table into the least amount of records, I can't use aggregate functions as certain columns, e.g. OPTION 2, can have more than one value down.

I want to end up with a table like this:
Adriaan | Jack | Egg | Yes | 34 | x
Adriaan | Jack | Egg | Yes | 34 | x
Joe | | Bob | No | 31 | xy
Joe | | Bob | null | 34 | null
Joe | | Bob | null | 36 | null
Joe | | Bob | null | 37 | null
Joe | | Bob | null | 11 | null
 
xrapidx said:
Ok, this is annoying me, so I thought I'd try here, since a few of you might have some spare time,

I wrote a SQL Query to pivot a table using CASE statements,

I now have a table that looks like this
FIRST NAME | MIDDLE NAME | LAST NAME | OPTION 1 | OPTION 2 | OPTION 3 (all the way to 285, but this changes)
Adriaan | Jack | Egg | Yes | null | null
Adriaan | Jack | Egg | null | 34 | null
Adriaan | Jack | Egg | null | null | x
Joe | | Bob | No | null | null
Joe | | Bob | null | 31 | null
Joe | | Bob | null | 34 | null
Joe | | Bob | null | 36 | null
Joe | | Bob | null | 37 | null
Joe | | Bob | null | 11 | null
Joe | | Bob | null | null | yx


How do I collapse the table into the least amount of records, I can't use aggregate functions as certain columns, e.g. OPTION 2, can have more than one value down.

I want to end up with a table like this:
Adriaan | Jack | Egg | Yes | 34 | x
Adriaan | Jack | Egg | Yes | 34 | x
Joe | | Bob | No | 31 | xy
Joe | | Bob | null | 34 | null
Joe | | Bob | null | 36 | null
Joe | | Bob | null | 37 | null
Joe | | Bob | null | 11 | null
Click to expand...

First, the table you say you want to end up with doesn't look right. If you're collapsing Adriaan Egg the same way as Joe Bob then the result should look like this:

Adriaan | Jack | Egg | Yes | 34 | x
Joe | | Bob | No | 31 | xy
Joe | | Bob | No | 34 | xy
Joe | | Bob | No | 36 | xy
Joe | | Bob | No | 37 | xy
Joe | | Bob | No | 11 | xy

not quite sure if this is what you're looking for. It looks like you're looking for the first non-null value in the column?

If it is then you can use the FIRST() aggregate function on a subquery that excludes the null values.
 
I usually build a temp table and populate it.
Do an insert for the "static info" and then an update for each Option
Always easier, more intuitive, and easier to maintain/debug.
 
Deenem said:
First, the table you say you want to end up with doesn't look right. If you're collapsing Adriaan Egg the same way as Joe Bob then the result should look like this:

Adriaan | Jack | Egg | Yes | 34 | x
Joe | | Bob | No | 31 | xy
Joe | | Bob | No | 34 | xy
Joe | | Bob | No | 36 | xy
Joe | | Bob | No | 37 | xy
Joe | | Bob | No | 11 | xy

not quite sure if this is what you're looking for. It looks like you're looking for the first non-null value in the column?

If it is then you can use the FIRST() aggregate function on a subquery that excludes the null values.
Click to expand...

Your table format will also work - Basically just trying to clean up the table, because currently with the 285 options staggered, it means 285 records per person instead of +-5

Only problem - MSSSQL doesn't seem to have a FIRST(), busy looking for the equivelant.

Moederloos said:
I usually build a temp table and populate it.
Do an insert for the "static info" and then an update for each Option
Always easier, more intuitive, and easier to maintain/debug.
Click to expand...

Problem is there's 285 options, and well over a million records, so it might take awhile.
 
Moederloos said:
I usually build a temp table and populate it.
Do an insert for the "static info" and then an update for each Option
Always easier, more intuitive, and easier to maintain/debug.
Click to expand...

Was also thinking that, but he won't know how many Joe Bob's to insert into the temp table.

Maybe one temp table with the unique names and then a second temp table with the collapsed columns and a join at the end?
 
I've never looked at the data warehousing functionality in SQL, but it looks like you need some of that, CUBE's and thing like that.
 
xrapidx said:
Your table format will also work - Basically just trying to clean up the table, because currently with the 285 options staggered, it means 285 records per person instead of +-5

Only problem - MSSSQL doesn't seem to have a FIRST(), busy looking for the equivelant.



Problem is there's 285 options, and well over a million records, so it might take awhile.
Click to expand...

I would write an application in .NET or Java and use something like Hibernate.
Then write this back to the database. But then again this is where my strength lies and not so much with writing procs.
 
Deenem said:
Was introduced in 2005 according to the M$ website.
Click to expand...

Nope - using 2005 - Says unrecognized function. Maybe I'm doing something wrong.

Deenem said:
I've never looked at the data warehousing functionality in SQL, but it looks like you need some of that, CUBE's and thing like that.
Click to expand...

I'm more into the Analysis Services side of Data Warehousing.

Usually it takes me 5 minutes to do something like this, but been blank this week because I've only been sleeping for 2-3 hours thanks to the damn house move.
 
I'll look into it - thanks.

On another project for two weeks so will only get back to that one later.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X