Post reply

Denormalizing a list of names

  • May 13, 2008 at 12:25 pm

    #185409

    Hello,

    If I have a table structured like this:

    [font="Courier New"]Company Employee

    --------- ------

    IBM Ron

    IBM Rachel

    Microsoft John

    Microsoft Jane[/font]

    and I want my results to look like this:

    [font="Courier New"]

    Company EmployeeList

    --------- -------------

    IBM 'Ron Rachel'

    Microsoft 'John Jane'[/font]

    Any ideas on how to accomplish this? I know how to do this for a single company. i.e.:

    declare @EmployeeList varchar(100),

    @ThisCompany varchar(10)

    set @EmployeeList = ''

    set @ThisCompany = 'IBM'

    select @EmployeeList = @EmployeeList + Employee + ' ' where Company = @ThisCompany

    select @ThisCompany, @EmployeeList

    But not for all the companies that appear in the table.

  • May 13, 2008 at 12:29 pm

    #814730

    You may try to use cursor.

  • May 13, 2008 at 12:33 pm

    #814732

    Well I was trying to be a "good boy" and not use a cursor. Is it not possible without using a cursor?

  • May 21, 2008 at 3:54 pm

    #818899

    Let me see whether or not I can help the "good boy" :hehe:

    SELECT Company, MAX(Employee) FROM yourTable GROUP BY Company

  • May 21, 2008 at 9:40 pm

    #818970

    I don't believe that works the way the OP posted... that will only give 1 employee name per company.

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

  • May 21, 2008 at 9:41 pm

    #818971

    wgarces,

    See the following article... it covers this type of concatenation and a couple of related pitfalls to avoid...

    http://www.sqlservercentral.com/articles/Test+Data/61572/

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

  • May 22, 2008 at 9:57 am

    #819229

    Thanks Jeff. I will check out that article.

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

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