Select specific column values in SQL Server

Question

I have a SQL query which result me 7 rows under one column i.e 'COMPONENT' in this manner:

D-SAND
10 MM
20 MM
MSRC
SRC
WATER
SP 607

I want to select all these component into 1 row and just display in one report each time .... Is there any workaround for this please help.

D-SAND 10 MM 20 MM MSRC SRC WATER SP 607

Possible duplicate of [Concatenate many rows into a single text string?](http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string) — Mihai-Daniel Virna, Apr 20 '16 at 08:59

score 0 · Answer 1 · answered Apr 20 '16 at 08:59

0

You can use FOR XML PATH('') to get desired result.

SELECT LEFT(x.COMP.value('.', 'NVARCHAR(MAX)'), LEN(x.COMP.value('.', 'NVARCHAR(MAX)')) -1) as COMPONENT
FROM
(
    SELECT COMPONENT+', '
    FROM yourTable
    FOR XML PATH(''), TYPE
) x(COMP);

answered Apr 20 '16 at 08:59

irakliG.

176
10

I have never seen it written that way before @irakliG, impressive use of XQUERYs :) I personally prefer `STUFF` though `SELECT STUFF((SELECT ', ' + COMPONENTS FROM [yourTable] FOR XML PATH('')), 1, 2, '');` – Shaneis Apr 20 '16 at 19:00

Select specific column values in SQL Server

1 Answers1