June 2, 2017 at 10:54 am
My question is listed within the code below.
if object_id('tempdb..#t')is not null DROP TABLE #t;
if object_id('tempdb..#t2')is not null DROP TABLE #t2;
if object_id('tempdb..#t3')is not null DROP TABLE #t3;
Select * INTO #t2
FROM
(
Select 'ABC' as NAME , 'MAZDA' as CAR UNION
Select 'ABC' as NAME , 'TOYOTA' as CAR UNION
Select 'ABC' as NAME , 'HONDA' as CAR UNION
Select 'ABC' as NAME , 'EQUINOX' as CAR UNION
Select 'FF' as NAME , 'SUBARU' as CAR UNION
Select 'FF' as NAME , 'ISUZU' as CAR UNION
Select 'FF' as NAME , 'IMPALA' as CAR
)A;
----Select * FROM #t2
/* WHAT I NEED IS THE OUTPUT as FOLLOWS - HELP ME WITH THE SELECT STMT */
ABC EQUINOX,MAZDA,HONDA,TOYOTA
FF IMPALA,ISUZU,SUBARU
June 2, 2017 at 11:35 am
Here you go.
https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 2, 2017 at 11:40 am
SELECT DISTINCT Name ,
STUFF(( SELECT ',' + Car
FROM #t2 t2
WHERE t2.Name= t1.Name
ORDER BY Name
FOR XML PATH('')
), 1, 1, '') as CarList
FROM #t2 AS t1;
Sue
June 2, 2017 at 1:42 pm
Sue_H - Friday, June 2, 2017 11:40 AMThere are a few different ways. Here is one:
SELECT DISTINCT Name ,
STUFF(( SELECT ',' + Car
FROM #t2 t2
WHERE t2.Name= t1.Name
ORDER BY Name
FOR XML PATH('')
), 1, 1, '') as CarList
FROM #t2 AS t1;Sue
Thx That worked!
June 3, 2017 at 5:41 pm
Here's an article by Wayne Sheffield that explains the great technique. http://www.sqlservercentral.com/articles/comma+separated+list/71700/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply