-1

Consider a table in SQL Server has a column Item, I tried to created a query to covert all values in the column into a single cell result into comma separated string, for example:

Table:

| Item |
--------
| milk |
| sugar|
| salt |
| .... |

Query result:

|       Item List         |
---------------------------
| milk, sugar, salt ..... |

It would be better to have some customization as

|                    Item List                     |
----------------------------------------------------
| milk as milk, sugar as sugar, salt as salt ..... |

I saw there is option as unpivot however it has to be definitive amount of values in the column. Just wondering any available functions to support this goal if target column value is dynamic

Dale K
  • 25,246
  • 15
  • 42
  • 71
Dreamer
  • 7,333
  • 24
  • 99
  • 179
  • 2
    Pretty much a duplicate of [this](https://stackoverflow.com/questions/194852/how-to-concatenate-text-from-multiple-rows-into-a-single-text-string-in-sql-serv). Your desire for customization is referenced by it as well. – pwilcox Jun 14 '21 at 22:52

1 Answers1

2

Consider you want to populate:

declare @csv nvarchar(max);

If you have sql server 2016 or earlier:

set @csv = 
    stuff((
        select  ',' + item + ' as ' + item
        from    @data
        for     xml path('')
    ),1,1,'');

If you have sql server 2017 or later:

set @csv = (
    select  string_agg (item + ' as ' + item, ',')
    from    @data
);
pwilcox
  • 5,542
  • 1
  • 19
  • 31
  • 1
    @DaleK. You're right. I'll look up a good dupe and vote to close. – pwilcox Jun 14 '21 at 22:50
  • And if you are going to show `for xml` at least do it properly: `', ' + ` OP wants an extra space. `for xml path(''), type).value('text()[1]','nvarchar(max)'),1,len(', '),'')` to prevent XML escaping – Charlieface Jun 14 '21 at 22:57