How to select 3 rows values in single column

Question

I have a table in SQL Server:

M_AllParty_HDR(ID, PartyName, AgentId, IsDeleted, IsActive)

I want to select all PartyName of any specific AgentID in single row and PartyName should be distinguish by Comma symbol. I have done following to solve this problem

select PartyName+',' 
from M_AllParty_HDR 
where AgentID=1613 and IsDeleted=0 and IsActive=1

The Output coming is

Rajesh mishra
Siddhi traders
Kamat tiwari

which is 3 rows. But i want output as follows:-

Rajesh mishra,Siddhi traders,Kamat tiwari

How is it possible? Please help me someone here.

Possible duplicate of [How can I combine multiple rows into a comma-delimited list in SQL Server 2005?](http://stackoverflow.com/questions/180032/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-sql-server-2005) — Juan Carlos Oropeza, Nov 02 '16 at 12:54
another solution http://stackoverflow.com/questions/6899/how-to-create-a-sql-server-function-to-join-multiple-rows-from-a-subquery-into — Juan Carlos Oropeza, Nov 02 '16 at 12:55
@JuanCarlosOropeza I do not like the *Oh! It's duplicate!* reflex... I know, this is how SO is supposed to work, but the linked question has an answer accepted and on top which is old fashioned RBAR `SELECT @var=@var + ...`. The longer SO exists the worse... — Shnugo, Nov 02 '16 at 13:01
@JuanCarlosOropeza Your second link points to a *modern* answer, but will not deal with special characters correctly... — Shnugo, Nov 02 '16 at 13:04
I really like @Shnugo's answer. But there is a strong argument for performing actions like this in the presentation layer instead. — David Rushton, Nov 02 '16 at 13:22
@destination-data, we are using a reporting tool, which expects the report's data in one big plain table. Many people use output to tools like Excel or other table oriented tools. Just imagine a result-set with 100 rows and some (up to 5) words which should be concatenated. If you provide this *ready done*, it is 100 rows, otherwise several hundred rows. Further more there might be several concatenated columns leading to a cartesian product... I think there are good reasons to let the DB do this... — Shnugo, Nov 02 '16 at 13:26
A fair point, especially for reporting data. In fact, I often perform operations like this in the db myself. But I like to point out alternative approaches and views. — David Rushton, Nov 02 '16 at 14:19
Hi! Is this question solved? Do you need further help? Please allow me one hint: If this question is solved, it would be very kind of you, to tick the acceptance check below the (best) answer's vote counter. This will 1) mark this issue as solved 2) make it easier for followers to find the best solution 3) pay points to the answerer and 4) pay points to you. Since you've crossed the 15 points border yourself, you are - additionally - asked to vote on contributions. This is the SO-way to say thank you. Happy Coding! — Shnugo, Nov 07 '16 at 07:57

score 1 · Answer 1 · answered Nov 02 '16 at 12:55

With SQL-Server this is usually done using the fact, that an XML without element names will be concatenated text just as is

select stuff(
(
select ',' + PartyName
from M_AllParty_HDR 
where AgentID=1613 and IsDeleted=0 and IsActive=1
for xml path('')
),1,1,'');

The STUFF will cut away the leading ,.

If you might have forbidden characters (especially <, > or &) within your PartyNames, you can use this:

select stuff(
((
select ',' + PartyName
from M_AllParty_HDR 
where AgentID=1613 and IsDeleted=0 and IsActive=1
for xml path(''),TYPE).value('.','nvarchar(max)')
),1,1,'');

(untested...)

score 0 · Answer 2 · answered Nov 02 '16 at 13:07

You can try like this

SELECT
    p.agentid,
    STUFF((SELECT
        ',' + PartyName
    FROM M_AllParty_Hdr
    WHERE agentid = p.agentid
    FOR xml PATH ('')), 1, 1, '') AS CommaSeperated
FROM M_AllParty_Hdr p
WHERE agentid = 1613 AND IsDeleted = 0 AND IsActive = 1
GROUP BY p.agentid

Gujjar · Answer 3 · 2016-11-02T13:49:32.690

0

Method 1: The easiest way to do

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + PartyName 
FROM M_AllParty_HDR
WHERE PartyName IS NOT NULL and AgentID=1613 and IsDeleted=0 and IsActive=1
print @Names

Method 2: For better performance.

SELECT STUFF((SELECT PartyName +','
    FROM M_AllParty_HDR WHERE PartyName IS NOT NULL and AgentID=1613 and IsDeleted=0 and IsActive=1
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

edited Nov 02 '16 at 13:49

answered Nov 02 '16 at 13:13

Gujjar

367
1
5
24

2

This might be *easy* (personal point of view), but it is RBAR and only useable in *multi-statement* procedural approaches... And it will be slow... – Shnugo Nov 02 '16 at 13:21
yes you r right. I added other method for faster execution. – Gujjar Nov 02 '16 at 13:42
Now it's a plain copy of existing answers :-) Never mind and happy coding! – Shnugo Nov 02 '16 at 13:44

How to select 3 rows values in single column

3 Answers3