COMMA SEPARATED VALUES - SQL Server Forums

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

General SQL Server Forums

New to SQL Server Programming

COMMA SEPARATED VALUES

Author

Topic

christosds
Starting Member

10 Posts

Posted - 2012-10-30 : 07:14:07

Hello
i have 2 tables. First table (MTRL) contains products

MTRL |CODE |NAME |CODE1 |CODE2 |CCCODEOL
----------------------------------------------------
15241|1015241|Product|3165141940640|000112|
15242|1015245|Product|3165141660640|000662|

and the second table (ORCODES) contains some codes related to the products of the first table

C|MTRL |CODE
------------------
1|15241|441906091A
2|15241|441906091B
3|15241|443906087BB
4|15242|443906087BD
5|15242|443906091

Can anyone help me add all the codes from the ORCODES.CODE field in second table in the MTRL.CCCODEOL field in the first table, separated with coma?

for example for product with MTRL=15214 to fill the field MTRL.CCCODEOL in first table with 441906091A, 441906091B, 443906087BB,

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 07:18:31

Just looking at the second table, you can generate the comma-separated values for each MTRL value as shown below. I didn't quite follow what you wanted to do with that - whether to update the first table, or do something else?

SELECT
	a.MTRL,
	STUFF(b.CODES,1,1,'') AS Codes
FROM
	(SELECT DISTINCT MTRL FROM ORCCODES) a
	CROSS APPLY
	(
		SELECT ','+b.CODE
		FROM ORCCODES b
		WHERE b.MTRL = a.MTRL
		FOR XML PATH('')
	)b(Codes)

christosds
Starting Member

10 Posts

Posted - 2012-10-30 : 07:32:05

Hello and thanks for the reply.

I want to generate the comma-separated values for each MTRL and then update the field CCCODEOL in first table with them.

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 07:41:10

First, run the first code - which will show you what the current values are and what it is going to be updated to. If you are happy with it, run the second code.

--- 1
SELECT t.MTRL,t1.CCCODEOL,STUFF(t2.Codes,1,1,'') AS NewCCCODEOL
FROM
	MTRL t1
	CROSS APPLY
	(
		SELECT ','+b.CODE
		FROM ORCCODES b
		WHERE b.MTRL = t1.MTRL
		FOR XML PATH('')
	)b(Codes)

--- 2
UPDATE t1 SET
	CCCODEOL = STUFF(t2.Codes,1,1,'')
FROM
	MTRL t1
	CROSS APPLY
	(
		SELECT ','+b.CODE
		FROM ORCCODES b
		WHERE b.MTRL = t1.MTRL
		FOR XML PATH('')
	)b(Codes)

christosds
Starting Member

10 Posts

Posted - 2012-10-30 : 09:09:52

It worked like a charm.
Thank you

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-10-30 : 10:28:46

Very welcome -:)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources