Results 1 to 8 of 8
  1. #1
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270

    Unanswered: Multiple row data into single cell

    Hi I want to convert the multiple row data into a single cell....

    e.g

    Table1
    -------
    COL1, COL2
    1, aa
    2, dd
    3, gg
    4, ff
    5, jj

    When I run SELECT COL2 FROM Table1, I get the following result set

    COL2
    -----
    aa
    dd
    gg
    ff
    jj


    But I want to get the result set as follows...

    (aa,dd,gg,ff,jj)


    Can you suggest me some approach.


    I don't want to use any cursor, table variable or CTE.

    I am lokking for soe easy solution.

    I am using SQL SERVER 2008


    Thanks,
    Rahul Jha

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by DBA_Rahul View Post
    I don't want to use any cursor, table variable or CTE.
    Why?

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    @Pat

    WHY? (Not to use CTE, Table Variable or CURSOR)

    ANS: Because the requirement is for a nightly batch job. And using CURSOR will consume lot of memory of the the live db server.



    Thanks,
    Rahul Jha

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If the goal is to avoid both standards and best practice, you can acheive that using:
    Code:
    DECLARE @c		NVARCHAR(937)
    DECLARE @t TABLE (col1 INT, col2 nvarchar(5))
    
    INSERT INTO @t (col1, col2)
       SELECT 1, 'aa' UNION
       SELECT 2, 'dd' UNION
       SELECT 3, 'gg' UNION
       SELECT 4, 'ff' UNION
       SELECT 5, 'jj'
    
    SELECT @c = Coalesce(@c + ', ', '(') + col2
       FROM @t
       ORDER BY col1
    
    SELECT @c + ')'
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  5. #5
    Join Date
    May 2004
    Location
    bangalore
    Posts
    270
    Thanks Pat for the suggestion....

    Thanks,
    Rahul Jha

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by DBA_Rahul View Post
    @Pat

    WHY? (Not to use CTE, Table Variable or CURSOR)

    ANS: Because the requirement is for a nightly batch job. And using CURSOR will consume lot of memory of the the live db server.



    Thanks,
    Rahul Jha
    Good Lord, Rahul. You've been on this forum for quite a while. Isn't it about time you started paying attention to best practices?
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Rahul, I'm curious, why do have you lumped CTEs in with cursors in terms of poor performance?
    George
    Home | Blog

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ...or that the issue is that cursors will consume a lot of memory.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •