Post reply

sql script assistance

  • February 5, 2010 at 7:21 am

    #220272

    drop table #temp

    create table #temp

    (

    offerid int

    , Catg varchar(100)

    )

    insert into #temp( offerid, Catg )

    select 1, 'health' union all

    select 1, 'fitness' union all

    select 1, 'weights' union all

    select 2, 'personal' union all

    select 3, 'yoga' union all

    select 3, 'health' union all

    select 3, 'stretch' union all

    select 4, 'weights' union all

    select 4, 'fitness' union all

    select 4, 'workout' union all

    select 4, 'stretch' union all

    select 5, 'fitness' union all

    select 5, 'health' union all

    select 6, 'health'

    --desired output

    offerid|catg

    1 | health, fitness, weights

    2 | personal

    3 | yoga, health, stretch

    4 | weights, fitness, workout, stretch

    5 | fitness, health

    6 | health

  • February 5, 2010 at 8:05 am

    #1114460

    here you go;

    this is using the FOR XML trick to concat your values together:

    create table #temp

    (

    offerid int

    , Catg varchar(100)

    )

    insert into #temp( offerid, Catg )

    select 1, 'health' union all

    select 1, 'fitness' union all

    select 1, 'weights' union all

    select 2, 'personal' union all

    select 3, 'yoga' union all

    select 3, 'health' union all

    select 3, 'stretch' union all

    select 4, 'weights' union all

    select 4, 'fitness' union all

    select 4, 'workout' union all

    select 4, 'stretch' union all

    select 5, 'fitness' union all

    select 5, 'health' union all

    select 6, 'health'

    SELECT offerid,stuff(( SELECT ',' + Catg

    FROM #temp s2

    WHERE s2.offerid= s1.offerid --- must match GROUP BY below

    ORDER BY Catg

    FOR XML PATH('')

    ),1,1,'') as [Categories]

    FROM #temp s1

    GROUP BY s1.offerid --- without GROUP BY multiple rows are returned

    ORDER BY s1.offerid

    drop table #temp

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • February 5, 2010 at 9:22 pm

    #1114915

    BaldingLoopMan (2/5/2010)

    drop table #temp

    create table #temp

    (

    offerid int

    , Catg varchar(100)

    )

    insert into #temp( offerid, Catg )

    select 1, 'health' union all

    select 1, 'fitness' union all

    select 1, 'weights' union all

    select 2, 'personal' union all

    select 3, 'yoga' union all

    select 3, 'health' union all

    select 3, 'stretch' union all

    select 4, 'weights' union all

    select 4, 'fitness' union all

    select 4, 'workout' union all

    select 4, 'stretch' union all

    select 5, 'fitness' union all

    select 5, 'health' union all

    select 6, 'health'

    --desired output

    offerid|catg

    1 | health, fitness, weights

    2 | personal

    3 | yoga, health, stretch

    4 | weights, fitness, workout, stretch

    5 | fitness, health

    6 | health

    BLM,

    I always have to ask when I see this type of denormalization... why do you need to do this? I mean, what are the business requirements?

    --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)
    Intro to Tally Tables and Functions

  • July 28, 2010 at 12:07 pm

    #1199420

    sorry, didnt realize u had question.,

    I dont recall what it was for. i think i was generating a file and the end user consuming this file wanted the categories in their own column delimited my columns

  • July 29, 2010 at 7:23 am

    #1199780

    BaldingLoopMan (7/28/2010)

    sorry, didnt realize u had question.,

    I dont recall what it was for. i think i was generating a file and the end user consuming this file wanted the categories in their own column delimited my columns

    Heh... thanks for the feedback but that's not actually the business reason I was hoping for. It's always some user/customer somewhere that wants this type of denormalization... what I really like to find out is why they think they want (for example) the categories in their own comma delimited columns. IE, what in the heck were they thinking and what are they actually going to do with the result?

    Anyway... I realize this was a while ago and that the customer (and the real reason behind the request) are probably long gone. Thanks for trying.

    --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)
    Intro to Tally Tables and Functions

Viewing 5 posts - 1 through 4 (of 4 total)

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