asked on

TSQL - Concatenate values into one string

Hi Experts,

I want to be able to put all records in one column/String, SQL Server 2008 R2

Right now I have this:

Join ( Select Contact, (@Concat = @Concat + LEFT(',', LEN(@Concat)) + ISNULL([Description], ''))  From tblNAsLists) AS List On C.Contact = List.Contact

Open in new window

But this is not correct.

Please help and thanks....

Kyle Abrahams, PMP

select 
c.*, STUFF(
(
SELECT ',' + ISNULL([Description], '')
FROM tblNAsLists list where 
c.Contact = List.Contact  ORDER BY list.contact FOR XML PATH('')), 1,1,'') )
) tblNADescs
from <tbl> c

Open in new window

Amour22015

ASKER

Ok so right now I have this:

SELECT        CompanyName, Aff.[Address], Aff.Cntr3, Aff.Assn, Aff.Code2, Aff.Date1, Aff.Date2, Aff.Date6, 
				list.[Description], Memo1
FROM            tblNAsAffiliations Aff Join 
				fncUtlAddress() ADDR ON ADDR.Contact = Aff.Contact1 Join
				tblNAsMasterCodes MC ON MC.GroupType = Aff.Affiliation And MC.GroupCode = Aff.Code1 And MC.MasterCode = Aff.code2 Join
				tblNAsContacts C ON C.Contact = Aff.Contact1 Join tblNAsLists AS List On C.Contact = List.Contact And List.GroupType = 'RPQC' And List.GroupCode = '3ARPQC'
WHERE     	Aff.Affiliation = 'RPQC' And Aff.Code1 = 'RPQC' And Aff.Code3 = 'Approved' And Aff.Date2 > dbo.fncUtlGracePeriodApply(GetDate(), MC.Instructions)

Open in new window

How do I make all fit into what I currently have?

Please help and thanks...

Kyle Abrahams, PMP

try the following:

SELECT        CompanyName, Aff.[Address], Aff.Cntr3, Aff.Assn, Aff.Code2, Aff.Date1, Aff.Date2, Aff.Date6, 
Memo1,
STUFF (
(
SELECT ',' + ISNULL([Description], '')
FROM tblNAsLists list 
where 
c.Contact = List.Contact And List.GroupType = 'RPQC' And List.GroupCode = '3ARPQC'
ORDER BY list.contact FOR XML PATH('')
)
, 1,1,'')  tblNADescs

FROM            tblNAsAffiliations Aff Join 
				fncUtlAddress() ADDR ON ADDR.Contact = Aff.Contact1 Join
				tblNAsMasterCodes MC ON MC.GroupType = Aff.Affiliation And MC.GroupCode = Aff.Code1 And MC.MasterCode = Aff.code2 Join
				tblNAsContacts C ON C.Contact = Aff.Contact1 
WHERE     	Aff.Affiliation = 'RPQC' And Aff.Code1 = 'RPQC' And Aff.Code3 = 'Approved' And Aff.Date2 > dbo.fncUtlGracePeriodApply(GetDate(), MC.Instructions)

Open in new window

Amour22015

ASKER

ok great but one little problem I need to put a space between each

Right now it is doing:
Cat,Dog,Mouse

I need:
Cat, Dog, Mouse

Please help

ASKER CERTIFIED SOLUTION

Kyle Abrahams, PMP

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Amour22015

ASKER

Great thanks for your help, on the last posting you sent, putting the space worked, but I am also using Access reports and I noticed that the #name error came up and only thing I can think about is that it reached the max for that field in Access. Thanks again...