Post reply

SQL GreenHorn issue... Combining table data to a concat string

  • May 3, 2011 at 10:53 am

    #249705

    I hope this is something simple and I just missed it, or I am trying to be too creative with my attempts.

    Situation... Table with Groups, LoginID, Type, Object Class.

    One LoginID has Many Groups....

    I am wanting to Query, Join and present the Groups as a Concat String with each member and Title Number....

    Here is what I have so far..

    SELECT DISTINCT Table1.LoginID,Table1.Groups,Table2.Title_Number

    FROM Table1

    INNER JOIN Table2

    ON Table1.LoginID = Table2.WinNT_ID

    Order by LoginID

    Results:

    LoginID Groups Title_Number

    aam1234 Position1 2041000000

    aam12234 Position2 2041000000

    aes4321 Position4 9308000000

    aes4321 Position5 9308000000

    What I would like to see:

    LoginID Groups Title_Number

    aam1234 Position1;Position2 2041000000

    aes4321 Position4;Position5 9308000000

    How do I take the Groups column and create the Concat String looking for Distinct Groups where LoginID and TitleNumber are the same?

  • May 3, 2011 at 11:03 am

    #1319245

    Check out this article: Creating a comma-separated list (SQL Spackle)[/url]

    Wayne
    Microsoft Certified Master: SQL Server 2008
    Author - SQL Server T-SQL Recipes

    If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!
    Links:
    For better assistance in answering your questions
    Performance Problems
    Common date/time routines
    Understanding and Using APPLY Part 1 & Part 2

  • May 3, 2011 at 11:06 am

    #1319246

    SELECT

    T.name,

    STUFF((

    SELECT

    ',' + name

    FROM

    sys.columns C

    WHERE C.object_id = T.object_id

    ORDER BY

    name

    FOR

    XML PATH('')

    ) , 1 , 1 , '') As Columns_

    FROM sys.tables T

    ORDER BY name

  • May 3, 2011 at 2:52 pm

    #1319413

    Thanks Wayne and Ninja, Exactly what I was looking for...

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

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