Loading ...

How to concatenate rows in sql | CodeAsp.Net

How to concatenate rows in sql

 /5
0 (0votes)


In this blog i will explain how to concatenate rows in SQL. I will show with two examples:

Below is the sample script:

DECLARE @MyTable TABLE
([ID] INT ,MyColumn VARCHAR(50))

INSERT INTO @MyTable
SELECT 1 , 'bsmith'
UNION ALL
SELECT 1 , 'csmith'
UNION ALL
SELECT 1 , 'dsmith'
UNION ALL
SELECT 2 , 'esmith'
UNION ALL
SELECT 2 , 'fsmith'
UNION ALL
SELECT 3 , 'gsmith'


--SQL2005 AND GREATER

SELECT DISTINCT [ID],
(STUFF((SELECT ',' + MyColumn FROM @MyTable emp
WHERE emp.[ID]=empnew.[ID]
ORDER BY [ID]
FOR XML PATH ('')),1,1,'')) AS MyColumn
FROM @MyTable empnew

--OUTPUT

--[ID] MyColumn

--1 bsmith,csmith,dsmith

--2 esmith,fsmith

--3 gsmith


--SECOND WAY


SELECT DISTINCT [ID],STUFF(M.m_list, 1, 1, '') AS MyColumn FROM @MyTable empnew
CROSS APPLY
(
SELECT ',' + MyColumn
FROM @MyTable emp
WHERE emp.[ID]=empnew.[ID]
ORDER BY [ID]
FOR XML PATH ('')) AS M (m_list)


--OUTPUT

--[ID] MyColumn

--1 bsmith,csmith,dsmith

--2 esmith,fsmith

--3 gsmith









Comments (no comments yet)

Top Posts