Post reply

Permutations of Columns in a Table

  • December 23, 2012 at 11:25 am

    #268513

    Hello,

    I have several columns, such as:

    Coulmn A

    red

    green

    yellow

    black

    blue green

    pink

    Column B

    shirt

    jumper

    blouse

    coat

    t-shirt

    skirt

    Column C

    for women

    for men

    for girls

    extra large

    for boys

    one size fits all

    I need to permutate, it such a way that gives me the following result:

    red shirt for women

    red shirt for men

    red shirt for girls

    red shirt extra large

    red shirt for boys

    red shirt one size fits all

    red jumper for women

    red jumper for men

    ...

    pink skirt for boys

    pink skirt one size fits all

    I was able to put each column in 3 separate tables, and then do a simple cross join but would like to have all the columns in one table and then the result.

    Thanks

  • December 23, 2012 at 5:36 pm

    #1571201

    VegasL (12/23/2012)

    pink skirt for boys

    There's just something so wrong with that! 😛

    The answer is to do a Cross Join between all the values of all 3 columns with 3 aliased copies of the same table. If you'd like a coded answer, please read and heed the article at the first link in my signature line below. Thanks.

    --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)

  • December 23, 2012 at 6:14 pm

    #1571208

    Hi John,

    Yes I agree something wrong with that. I'm working with large data set, didn't even notice.

    Anyways, I did read the link you posted on forum etiqutte so hopefully I am complying now.

    I put the data in .xls format for ease of use. Hopefully thats what you're were asking for.

    Yes, coded answer would be appreciated.

    Thanks

  • December 23, 2012 at 10:28 pm

    #1571220

    VegasL (12/23/2012)

    Hi John,

    Yes I agree something wrong with that. I'm working with large data set, didn't even notice.

    Anyways, I did read the link you posted on forum etiqutte so hopefully I am complying now.

    I put the data in .xls format for ease of use. Hopefully thats what you're were asking for.

    Yes, coded answer would be appreciated.

    Thanks

    First of all, the name is "Jeff". 😉

    Second, please read the article again. Having this stuff in a spreadsheet doesn't make it any easier than what you've already posted. We need a CREATE TABLE and some INSERT/SELECT statements to make "readily consumable data".

    --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)

  • December 24, 2012 at 6:17 pm

    #1571384

    Ok here's my attempt...

    select [Colors] + ' ' + [Items] +' ' + [Size] as 'Combined KW'

    from [dbo].[MYDB] as KW

    cross join [Colors] cross join [Items] cross join [Size]

    i'm trying to combine all 3 columns as 'combined kw'

    Thanks

  • December 28, 2012 at 4:56 pm

    #1572274

    After spending some time dabbling with this, I finally figured it out 🙂

    it required adding several self joins to the from line such as

    select color.[colors] + ' ' + itm.[items] + ' ' +sz. as 'Combined KW'

    from [dbo].[MYDB] as [Color] cross join [Items] as ITM cross join [Size] as SZ

  • January 4, 2013 at 1:00 pm

    #1573928

    create table #temp23(color nvarchar(566),item nvarchar(455),size nvarchar(332))

    insert into #temp23

    select 'red','shirt','for women' union all

    select 'green','jumper','for men' union all

    select 'yellow','blouse','for girls' union all

    select 'black','coat','extra large' union all

    select 'blue green','t-shirt','for boys' union all

    select 'pink','skirt','one size fits all'

    select * from #temp23

    create table #t(a nvarchar(566))

    insert into #t select color from #temp23

    create table #t1(b nvarchar(566))

    insert into #t1 select item from #temp23

    create table #t2(c nvarchar(566))

    insert into #t2 select size from #temp23

    select *,(a+' '+ b+' '+c) as total from #t cross join #t1 cross join #t2

    ---------------I am a begginer tried soft way--------------------------------

    VegasL (12/23/2012)

    Hello,

    I have several columns, such as:

    Coulmn A

    red

    green

    yellow

    black

    blue green

    pink

    Column B

    shirt

    jumper

    blouse

    coat

    t-shirt

    skirt

    Column C

    for women

    for men

    for girls

    extra large

    for boys

    one size fits all

    I need to permutate, it such a way that gives me the following result:

    red shirt for women

    red shirt for men

    red shirt for girls

    red shirt extra large

    red shirt for boys

    red shirt one size fits all

    red jumper for women

    red jumper for men

    ...

    pink skirt for boys

    pink skirt one size fits all

    I was able to put each column in 3 separate tables, and then do a simple cross join but would like to have all the columns in one table and then the result.

    Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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