Normalize a table query

Question

Normalize a table query

bonggoy

Grasshopper

Points: 19
More actions
August 19, 2010 at 2:23 pm

#236838

I have the following table:
ID, Description, Field1, Field2 ... Field9
Field1 to Field9 are all nullable fields.
I want a query to return as follows:
ID, Description, Field1
ID, Description, Field2
ID, Description, Field3
...
ID, Description, Field9
The row will be skipped if Fieldn is null.
Thanks.
Ronnie

Viewing 10 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 1

bonggoy cruz (8/19/2010)
I have the following table:
ID, Description, Field1, Field2 ... Field9
Field1 to Field9 are all nullable fields.
I want a query to return as follows:
ID, Description, Field1
ID, Description, Field2
ID, Description, Field3
...
ID, Description, Field9
The row will be skipped if Fieldn is null.
Thanks.
Ronnie

My recommendation would be to use UNPIVOT. The documentation for the command is in Books Online.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bonggoy Grasshopper Points: 19 More actions · Answer 2

bonggoy

Grasshopper

Points: 19

August 19, 2010 at 2:47 pm

#1209375

Thank you. I

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

You bet. Thanks for the feedback.

Also, since you're new to this forum, you might want to take a look at the first link in my signature line below. It could really make you're life easy on your next question. 🙂

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jayraj0071 SSC Journeyman Points: 86 More actions · Answer 4

Try soemthing like this!!!

select ID, [Description], Field =

case when ID = 1 then Field1

when ID = 2 then Field2

when ID = 3 then Field3

when ID = 4 then Field4

when ID = 5 then Field5

when ID = 6 then Field6

when ID = 7 then Field7

when ID = 8 then Field8

when ID = 9 then Field9

else null

end

from #test

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

jayraj0071 (8/24/2010)
Try soemthing like this!!!
select ID, [Description], Field =
case when ID = 1 then Field1
when ID = 2 then Field2
when ID = 3 then Field3
when ID = 4 then Field4
when ID = 5 then Field5
when ID = 6 then Field6
when ID = 7 then Field7
when ID = 8 then Field8
when ID = 9 then Field9
else null
end
from #test

Try that against the OP's orginal data which looks like the following...

ID, Description, Field1, Field2 ... Field9

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jayraj0071 SSC Journeyman Points: 86 More actions · Answer 6

jayraj0071

SSC Journeyman

Points: 86

August 24, 2010 at 6:34 pm

#1211392

somebody provide sample data of table!!!

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

jayraj0071 (8/24/2010)
somebody provide sample data of table!!!

Heh... Why? OP is happy with the answer to lookup UNPIVOT. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

bonggoy Grasshopper Points: 19 More actions · Answer 8

I did use unpivot. I was actually aware and have used PIVOT before. I felt kind of stupid when UNPivot was suggested :D. Sometimes the most obvious answer is the one you overlook.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 9

bonggoy cruz (8/24/2010)
I did use unpivot. I was actually aware and have used PIVOT before. I felt kind of stupid when UNPivot was suggested :D. Sometimes the most obvious answer is the one you overlook.

Nah... definitely not stupid. We all forget things now and again. 🙂

Thanks for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)