How to concatenate rows into 1 column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to concatenate rows into 1 column

Author

Topic

nayanancha
Starting Member

27 Posts

Posted - 2011-11-03 : 13:59:40

I have a stored proc which brings down the folowing result:

I want the column to coming in a single row with comma seperated values.

Thanks,

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER proc [dbo].[proc_GetFrontEndVendors]
@Mode varchar(10)
as
begin
declare
@CompanyTable varchar(200),
@CompanyVendorTable varchar(200),
@FrontendVendorTable varchar(200),
@Sql nvarchar(1000),
@Param nvarchar(1000)

if(@Mode='live')
begin
set @FrontendVendorTable = 'Portal.dbo.FrontendVendor'
set @CompanyTable = 'Portal.dbo.Company'
set @CompanyVendorTable = 'Portal.dbo.CompanyVendor'
end
else
begin
set @FrontendVendorTable = 'Portal_Sandbox.dbo.FrontendVendor'
set @CompanyTable = 'Portal_Sandbox.dbo.Company'
set @CompanyVendorTable = 'Portal_Sandbox.dbo.CompanyVendor'
end

set @Sql = N'select
FV.Id,
FV.VendorName,
FV.VendorCode,
FV.VendorAddress,
FV.ContactPerson,
FV.ContactEmail,
FV.ContactPhone,
C.CompanyCode

from ' + @CompanyTable + ' C inner join '
+ @CompanyVendorTable + ' CV on C.Id = CV.CompanyId inner join '
+ @FrontendVendorTable + ' FV on CV.VendorId = FV.Id
where
FV.active=1 order by FV.VendorName'

exec sp_executesql @Sql
end
/*
proc_GetFrontEndVendors 'sandbox'
*/
Output:

ID VendorName CompanyCode
1 aaa A1
1 aaa A2
2 bbb B1
2 bbb B2

But I want the output as:
1 aaa A1,A2
2 bbb B1,B2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 14:05:41

see

http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources