| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
02-27-03, 09:00
|
|
Registered User
|
|
Join Date: Feb 2003
Location: Dayton, OH
Posts: 8
|
|
|
Seperating a record into multiple
|
|
Okay, I have a table (called "Sizes") that looks like so:
Size____Type1____Type2____Type3
27"______1________0________1
85"______0________1________0
How do I pull
Size_____Type
27"______Type1
27"______Type3
85"______Type2
Select ???? from Sizes ????
Thanks,
riddelrp
|
|
02-27-03, 09:55
|
|
Registered User
|
|
Join Date: Feb 2003
Location: Dayton, OH
Posts: 8
|
|
|
Reverse Pivot Table
Sounds like what I need is a reverse pivot table... now how do I do that?!
Quote:
Originally posted by riddelrp
Okay, I have a table (called "Sizes") that looks like so:
Size____Type1____Type2____Type3
27"______1________0________1
85"______0________1________0
How do I pull
Size_____Type
27"______Type1
27"______Type3
85"______Type2
Select ???? from Sizes ????
Thanks,
riddelrp
|
|
|
02-27-03, 10:04
|
|
Registered User
|
|
Join Date: Feb 2002
Location: Houston, TX
Posts: 809
|
|
|
|
something like this?
Code:
create table #Sizes(Size varchar(5), Type1 bit, Type2 bit, Type3 bit)
insert into #Sizes values('27"',1,0,1)
insert into #Sizes values('28"',0,1,0)
select * From #Sizes
select Size, 'Type1' as Type from #Sizes where Type1 > 0
union
select Size, 'Type2' as Type from #Sizes where Type2 > 0
union
select Size, 'Type3' as Type from #Sizes where Type3 > 0
order by 1,2
__________________
Paul Young
(Knowledge is power! Get some!)
|
|
02-27-03, 10:11
|
|
Registered User
|
|
Join Date: Feb 2003
Location: Germany
Posts: 53
|
|
Re: Seperating a record into multiple
the following will work:
select size, 'Type1' from sizes where type1=1
union
select size, 'Type2' from sizes where type2=1
union
select size, 'Type3' from sizes where type3=1
order by 1
|
|
02-27-03, 10:49
|
|
Registered User
|
|
Join Date: Feb 2003
Location: Dayton, OH
Posts: 8
|
|
Very cool, thanks... 1 last question.
what does order by 1,2 do?
Thanks,
Ryan
PS: I was starting to write a stored proc where I could dump all the Type1s in then all the Type2s then all the Type3s into a temp table then pull all records in that table (the union thing is MUCH easier)
thanks
|
|
02-27-03, 10:51
|
|
Registered User
|
|
Join Date: Feb 2002
Location: Houston, TX
Posts: 809
|
|
order by 1 or order by 1,2 = order by result set column 1, column 2 etc.
__________________
Paul Young
(Knowledge is power! Get some!)
|
|
02-27-03, 10:52
|
|
Registered User
|
|
Join Date: Feb 2003
Location: Germany
Posts: 53
|
|
Quote:
Originally posted by riddelrp
Very cool, thanks... 1 last question.
what does order by 1,2 do?
Thanks,
Ryan
PS: I was starting to write a stored proc where I could dump all the Type1s in then all the Type2s then all the Type3s into a temp table then pull all records in that table (the union thing is MUCH easier)
thanks
|
order by just oders the resultset 
order by 1,2 means: order by "column1" , "column2"
|
|
02-27-03, 10:57
|
|
Registered User
|
|
Join Date: Feb 2003
Location: Dayton, OH
Posts: 8
|
|
oh wow, ok I get it, it orders by the column number... I'm sorry, I've just never done an order by statement using col numbers before
riddelrp making a name for himself on dbforums after just 4 posts... even if it isn't a good name :-/
-riddelrp
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Seperating a record into multiple
