#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    7
    Rep Power
    0

    Creating subsets out of the same SQL table


    if you have:
    1
    2
    3

    you can come up with subsets like
    1 2
    1 3
    2 1
    2 3
    3 1
    3 2

    i have a table
    ITEM1 ITEM2 ITEM3
    1 0 0
    2 0 0
    3 0 0
    5 0 0
    1 3 0
    2 3 0
    2 5 0
    3 5 0
    2 3 5

    I have to create a table that will take each row and create a subset within the same table so that i will have 6 columns (# of orig columns x 2) with the ff data

    1 2 0 0 0 0
    1 3 0 0 0 0
    1 5 0 0 0 0
    1 1 3 0 0 0
    1 2 3 0 0 0
    1 2 5 0 0 0
    1 3 5 0 0 0
    1 2 3 5 0 0
    2 1 0 0 0 0
    2 3 0 0 0 0
    2 5 0 0 0 0
    2 1 3 0 0 0
    2 2 3 0 0 0
    2 2 5 0 0 0
    2 3 5 0 0 0
    2 2 3 5 0 0
    ..and so on

    PLEASE, PLEASE HELP..
    thank you so much.
  2. #2
  3. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2012
    Posts
    7
    Rep Power
    0

    Creating subsets out of the same SQL table


    if you have:
    1
    2
    3

    you can come up with subsets like
    1 2
    1 3
    2 1
    2 3
    3 1
    3 2

    i have a table
    ITEM1 ITEM2 ITEM3
    1 0 0
    2 0 0
    3 0 0
    5 0 0
    1 3 0
    2 3 0
    2 5 0
    3 5 0
    2 3 5

    I have to create a table that will take each row and create a subset within the same table so that i will have 6 columns (# of orig columns x 2) with the ff data

    1 2 0 0 0 0
    1 3 0 0 0 0
    1 5 0 0 0 0
    1 1 3 0 0 0
    1 2 3 0 0 0
    1 2 5 0 0 0
    1 3 5 0 0 0
    1 2 3 5 0 0
    2 1 0 0 0 0
    2 3 0 0 0 0
    2 5 0 0 0 0
    2 1 3 0 0 0
    2 2 3 0 0 0
    2 2 5 0 0 0
    2 3 5 0 0 0
    2 2 3 5 0 0
    ..and so on

    PLEASE, PLEASE HELP..
    thank you so much.
  4. #3
  5. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,120
    Rep Power
    4274
    may i suggest some research on CROSS JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo