Concatenating Column Values into a Comma-Separated List

Rédigé par Sozezzo - - Aucun commentaire

We can easily concatenate column values into a comma-separated list with a variable, but this solution we can avoid to use a variable.


PRINT '-- Concatenating Column Values into a Comma-Separated List'


--#region Create my test table

begin try drop table YourTable ; end try begin catch end catch -- begin try catch

create table YourTable (colKey nvarchar(50), colVal nvarchar(1), colOrderBy int);

insert YourTable values ('a',3, 1), ('a', 1, 2), ('b',8, 3), ('b',7, 1), ('b',6, 2);

--#endregion Create my test table






       SELECT (', ' + CAST(tbDetail.colVal AS NVARCHAR))  -- you do not neew CAST if you use a string type.

       FROM YourTable tbDetail(NOLOCK)

       WHERE tbDetail.colKey = o.colKey

       ORDER BY tbDetail.colOrderBy  -- if you want, you can order by your columns

       FOR XML PATH('')

       ), 1, 2, '') MyStuffList

FROM YourTable o(NOLOCK)

Source :



Les commentaires sont fermés.