none
query results as string

    Question

  • Hi,

    We are using SQL Server 2005
    I have a select query like this:

     Select  distinct(column1) From table1 t1 WHERE t1.status in ('AAA', 'DDDD')
    except
    select column1 from @tmptable

    The results are:

    Quat123
    Qaty345
    uios567

    I want the result set as string Quat123,Qaty345,uios567

    Simple sql, I can do this. But with distinct, it is not working. I can get the result as string by inserting full result into another tmp table and do another select query for string. I am trying to avoid that. I appreciate your response. Column1 can have null values.

    Thanks,
    Spunny

    Wednesday, September 25, 2013 2:47 AM

Answers

  • Declare @t1 table(column1 varchar(10), status varchar(10));
    Insert @t1(column1, status) Values
    ('Quat123', 'AAA'), 
    ('Quat123', 'DDDD'), 
    ('Qaty345', 'AAA'), 
    ('uios567', 'DDDD'), 
    ('xxx', 'zzz'), 
    ('yyy', 'DDDD'); 
    Declare @tmptable table(column1 varchar(10));
    Insert @tmptable(column1) Values('yyy');
    
    ;With cte As
    (Select  distinct(column1) As column1 From @t1 t1 WHERE t1.status in ('AAA', 'DDDD')
    except
    select column1 from @tmptable)
    Select STUFF((SELECT ',' + column1  
                        from cte
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'');

    Tom
    • Marked as answer by Spunny Wednesday, September 25, 2013 6:37 PM
    Wednesday, September 25, 2013 3:24 AM
  • Thank you Jingyangli. Your query also worked.
    • Marked as answer by Spunny Wednesday, September 25, 2013 6:37 PM
    Wednesday, September 25, 2013 6:37 PM

All replies

  • There are several ways to convert data from a column into a comma separated list. One method that I have used a number of times is to use the XML function. See this example.

    DECLARE @t TABLE (Val VARCHAR(10))
    INSERT INTO @t (Val)
    SELECT 'Quat123' UNION ALL
    SELECT 'Qaty345' UNION ALL
    SELECT 'uios567'
    
    SELECT SUBSTRING((
    	SELECT ',' + val AS 'text()'
    	FROM @t 
    	FOR XML PATH('')
    ), 2, 1024) AS Data
    
    /*
    Data
    --------------------------
    Quat123,Qaty345,uios567
    */


    My Blog

    Wednesday, September 25, 2013 2:58 AM
    Moderator
  • Try

    declare @id nvarchar(50)
    SELECT @id=stuff( (SELECT ', ' + column1
                   From table1 t1 WHERE t1.status in ('AAA', 'DDDD') 
                   ORDER BY column1
                   FOR XML PATH('')),1 ,2 ,'');
    go
    



    Many Thanks & Best Regards, Hua Min


    Wednesday, September 25, 2013 3:00 AM
  • Thank you for quick responses. It works as you suggested, but my query with distinct doesn't work.

    Select  distinct(column1) From table1 t1 WHERE t1.status in ('AAA', 'DDDD')
    except
    select column1 from @tmptable

    I need the result set of above query as string.

    Thanks,

    Spunny

    Wednesday, September 25, 2013 3:07 AM
  • Declare @t1 table(column1 varchar(10), status varchar(10));
    Insert @t1(column1, status) Values
    ('Quat123', 'AAA'), 
    ('Quat123', 'DDDD'), 
    ('Qaty345', 'AAA'), 
    ('uios567', 'DDDD'), 
    ('xxx', 'zzz'), 
    ('yyy', 'DDDD'); 
    Declare @tmptable table(column1 varchar(10));
    Insert @tmptable(column1) Values('yyy');
    
    ;With cte As
    (Select  distinct(column1) As column1 From @t1 t1 WHERE t1.status in ('AAA', 'DDDD')
    except
    select column1 from @tmptable)
    Select STUFF((SELECT ',' + column1  
                        from cte
                FOR XML PATH(''), TYPE
                ).value('.', 'NVARCHAR(MAX)') 
            ,1,1,'');

    Tom
    • Marked as answer by Spunny Wednesday, September 25, 2013 6:37 PM
    Wednesday, September 25, 2013 3:24 AM
  •  Select STUFF((SELECT ',' + column1  
     from   (select  distinct(column1) As column1 From @t1 t1 WHERE t1.status in ('AAA', 'DDDD') 
    AND column1 Not IN (select column1 from test) ) t
     FOR XML PATH('') ),1,1,'');

    Wednesday, September 25, 2013 1:59 PM
    Moderator
  • Thank you Tom. It worked. It took 3 seconds to complete.

    Wednesday, September 25, 2013 6:36 PM
  • Thank you Jingyangli. Your query also worked.
    • Marked as answer by Spunny Wednesday, September 25, 2013 6:37 PM
    Wednesday, September 25, 2013 6:37 PM