Show column data in a comma separated single line.

Question

I have a table in SQL and table has one column(NAME) and the values are in NAME column as
A
B
C
...
...
...
X
Y
Z

Then, I want to show data through SQL query in a single line like A, B, C, ..... X, Y, Z.

Anurag Singh

Vikram Agrawal · Answer

Hi Anurag you can accomplish your requirement by creating one Stored Procedure. see below for SP CREATE PROCEDURE GetSepratedValue ( @TableName Varchar(100) @ColumnName Varchar(100) @Delimeter Char(1) ) AS BEGIN DECLARE @TEMP TABLE (ID INT IDENTITYMYCOLUMN VARCHAR(200) ) DECLARE @TotRows INT DECLARE @Result Varchar(MAX)='' INSERT INTO @TEMP (MYCOLUMN) EXEC (N'SELECT ' + @ColumnName + ' FROM ' + @TableName) SET @TotRows = @@ROWCOUNT DECLARE @MIN INT = 1 WHILE(@MIN BEGIN SELECT @RESULT = @RESULT + MYCOLUMN + '' FROM @TEMP WHERE ID = @MIN SET @MIN +=1 END SELECT SUBSTRING(@Result1LEN(@RESULT)-1) END -- EXEC GetSepratedValue 'TestTable' 'TestColumn' '' Thanks.

Anurag Singh · Answer

Hi All Thanks for your valuable answers. Anurag Singh

Riddhi Valecha · Answer

Hi.. You can make a function and use LISTAGG. Call this function in your select query.

Khargesh Rajput · Answer

DECLARE @commavalue VARCHAR(Max); SELECT @commavalue = COALESCE(@commavalue + ' ' '') + CAST(Name AS varchar(20)) FROM topic C SELECT @commavalue

Manish Kumar Choudhary · Answer

try this Select '  ' + Name from tableName > for XML Path ( '' ) Type

Joginder Banger · Answer

I Hope its working as per your requirement. select STUFF((select ''''+rtrim(t.Name)from table Name t for XML Path(''''))11'''') Name