Post reply

T-SQL Help

  • April 7, 2008 at 1:28 pm

    #184268

    Hi my query returns the following result set.

    Name Id Dept Code

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

    Craig M. White ISN BF 0113

    Craig M. White ISN BF 0545

    Deryck Chase ISN BF 0113

    Diana Lyons ISN BF 0113

    Diana Lyons ISN BF 0545

    Hosey D Steve ISN BF 0113

    Hosey D Steve ISN BF 0545

    I need to modify the query such that it returns the result like this. Basically combining the values of column "code" into a single group for one distinct record.

    Name Id Dept Code

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

    Craig M. White ISN BF 0113, 0535

    Deryck Chase ISN BF 0113

    Diana Lyons ISN BF 0113, 0535

    Hosey D Steve ISN BF 0113 , 0535

  • April 7, 2008 at 6:25 pm

    #799489

    Could be done by using cursor, can it be done without cursor?

  • April 7, 2008 at 8:02 pm

    #799508

    Uh, huh... several different ways in the following URL along with some performance pit-falls to avoid...

    http://qa.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)
    Intro to Tally Tables and Functions

  • April 7, 2008 at 8:21 pm

    #799511

    I've seen this type of solution in a few posts around here also, providing this is SQL 2005.

    SELECT DISTINCT Name, ID, Dept,mylist

    FROM myTable t1

    CROSS APPLY

    (SELECT CONVERT(VARCHAR(10),Code) + ',' AS [text()]

    FROM myTable t2

    WHERE t2.Name = t1.Name

    FOR XML PATH('')) AS Dummy(myList)

  • April 7, 2008 at 8:40 pm

    #799515

    This would also work..

    select distinct name , id ,dept ,code = replace(

    (select code as [data()] from #test t

    where t.name = t1.name

    for xml PATH ('')

    ), ' ', ',')

    from #test t1

    NJ

  • April 7, 2008 at 8:51 pm

    #799520

    Ummm... be very careful using CROSS APPLY... it's just another way of making a "correlated subquery" which qualifies as RBAR and can be very slow.

    --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 6 posts - 1 through 5 (of 5 total)

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