Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Location
    Dayton, OH
    Posts
    8

    Unanswered: 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

  2. #2
    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?!

    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

  3. #3
    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!)

  4. #4
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53

    Cool 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

  5. #5
    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

  6. #6
    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!)

  7. #7
    Join Date
    Feb 2003
    Location
    Germany
    Posts
    53
    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"

  8. #8
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •