CODEWITHSUNDEEP
sql-server
Sunny Sandeep
Sunny Sandeep

Reputation: 1011

How to select 3 rows values in single column

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.

Upvotes: 2

Views: 1425

Answers (3)

Gujjar
Gujjar

Reputation: 367

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, '')

Upvotes: 0

Kannan Kandasamy
Kannan Kandasamy

Reputation: 13959

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

Upvotes: 0

Gottfried Lesigang
Gottfried Lesigang

Reputation: 67311

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...)

Upvotes: 1

Related Questions