SQL Server Performance

Concatenate Column data without using a cursor

Discussion in 'SQL Server 2008 General DBA Questions' started by AMOURGH, Mar 18, 2009.

  1. AMOURGH Member

    Hi Everybody ,
    I have a table
    DECLARE @ConcatStr varchar(Max)
    DECLARE @MyTable TABLE
    (
    Id int,
    Property varchar(50),
    Value DECIMAL(20,2)
    )
    INSERT INTO @MyTable
    SELECT 1,p1,2
    UNION ALL
    SELECT 2,p2,4
    UNION ALL
    SELECT 3,p3,5
    I wonna get in @ConcatStr=[p1],[p2],[p3],...,[pn]
    without using a cursor
    Any Ideas.Thanks in advance.
  2. Adriaan New Member

    If you had done some searching on concatenation, you would have found plenty of code examples here.
    SET @ConcatStr = ''
    SELECT @ConcatStr = @ConcatStr + CASE WHEN LEN(@ConcatStr) = 0 THEN '' ELSE "," END
    + @MyTable.Property
    FROM @MyTable
    ORDER BY @MyTable.Property
    SELECT @ConcatStr
  3. patel_mayur New Member

    DECLARE @Var VARCHAR(8000)
    SET @Var = ''
    SELECT @Var = @Var + [Property] + ',' FROM @MyTable
    SELECT @Var
  4. AMOURGH Member

    I have tested your suggestions.
    No one gives the result.it gives @ConcatStr=NULL
    Could u test please.I am surprised
  5. Adriaan New Member

    Then the Property column is nullable, and contains null values on certain rows.
    To skip those rows, add a WHERE clause.
    To include blanks in the comma-separated list, use:
    SELECT @ConcatStr = @ConcatStr + CASE WHEN LEN(@ConcatStr) = 0 THEN '' ELSE "," END
    + IS_NULL(@MyTable.Property, ' ')
    FROM @MyTable
    ORDER BY @MyTable.Property
  6. purumae New Member

    DECLARE @ConcatStr varchar(Max)
    DECLARE @MyTable TABLE (
    Id int,
    [Property] varchar(50),
    [Value] DECIMAL(20,2)
    )
    INSERT INTO @MyTable
    SELECT 1,'p1',2
    UNION ALL
    SELECT 2,'p2',4
    UNION ALL
    SELECT 3,'p3',5

    SELECT @ConcatStr = CASE WHEN @ConcatStr IS NULL THEN [Property] ELSE @ConcatStr + ',' + [Property] END
    FROM @MyTable
    WHERE [Property] IS NOT NULL
    SELECT @ConcatStr
  7. FrankKalis Moderator

    Have a look at this one: http://blogs.technet.com/wardpond/archive/2008/03/15/database-programming-the-string-concatenation-xml-trick-revisited-or-adam-is-right-but-we-can-fix-it.aspx
  8. dhamu294 New Member

    Hey Amourg
    look at this query. hope u want exactly this :)
    DECLARE @MyTable TABLE (
    Id int,
    [Property] varchar(50),
    [Value] DECIMAL(20,2)
    )
    INSERT INTO @MyTable
    SELECT 1,'p1',2
    UNION ALL
    SELECT 2,'p2',4
    UNION ALL
    SELECT 3,'p3',5
    declare @var varchar(3000)
    set @var = ''
    SELECT @Var = @Var + COALESCE (Property, '') + ',' from @MyTable
    select @var

Share This Page