SQL select multiple rows in one column
45,707
Solution 1
AFAIK, there is no native way to do so. However, you can use the FOR XML
to do this like so:
SELECT
t1.Id,
STUFF((
SELECT ', ' + t2.name
FROM Table1 t2
WHERE t2.ID = t1.ID
FOR XML PATH (''))
,1,2,'') AS Names
FROM Table1 t1
GROUP BY t1.Id;
SQL Fiddle Demo
This will give you:
| ID | NAMES |
----------------
| 1 | A, B, C |
| 2 | D, E |
| 3 | F |
Solution 2
try this ::
SELECT a.ID,
SUBSTRING(d.Name,1, LEN(d.Name) - 1) Name
FROM
(
SELECT DISTINCT ID
FROM testTable
) a
CROSS APPLY
(
SELECT [Name] + ', '
FROM testTable AS B
WHERE A.ID = B.ID
FOR XML PATH('')
) D (Name)
Author by
Gulrej
Updated on July 05, 2022Comments
-
Gulrej about 2 years
I have table TestTable
ID Name ------- 1 A 1 B 1 C 2 D 2 E 3 F
I want to write a query in SQL Server 2008 which will return
ID Name ---------- 1 A,B,C 2 D,E 3 F
Please someone help me to write this query.
-
Mahmoud Gamal over 11 yearsIt would be better if you show the OP how is the group concatenation can be done using the
PIVOT
table operator. -
Gulrej over 11 yearsHey Thanks a lot, its working.
-
Ivan Golović over 11 years@Gulrej If this solution resolved your problem, consider marking it as a correct answer.
-
ttomsen about 10 yearsPivot will only create more columns, not put the pivoted data into ONE column, do not go down this path if you want one column.