Concatenate multiple rows from one table, associated to column in second table having multiple values

Question

Concatenate multiple rows from one table, associated to column in second table having multiple values

mahler9

Mr or Mrs. 500

Points: 569
More actions
April 22, 2015 at 1:15 pm

#300100

Table1 contains a CODES column which may store multiple codes separated by commas.
The descriptions for these codes are stored individually in a column in in Table2.
I would like to return the descriptions from Table2 in the same format as the codes from Table1.
[font="Courier New"]
Table1
Stores multiple codes per ID in a single column.
ID CODES
-- --------------
01 Code1,Code2,Code3
Table2
Stores the definitions for the codes in Table1
CODE DESCRIPTION
----- ---------------
Code1 Description1
Code2 Description2
Code3 Description3
[/font]
The tables are what the tables are. Here is what I am trying to accomplish:
[font="Courier New"]
ID CODES DESCRIPTIONS
-- ----------------- --------------------------------------
01 Code1,etc Description1,Description2,Description3
[/font]
I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?
Not a DBA, rather a humble report writer stuck at this point.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 1

I've found two different queries which return each code and associated description by ID in separate rows. Perhaps the next step is to aggregate from this?
Not a DBA, rather a humble report writer stuck at this point.

Then FOR XML PATH('') is the aggregate you need.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 2

Something like,

CREATE TABLE #ids(id int, codes varchar(30))

CREATE TABLE #codes(code varchar(30), descript varchar(30))

INSERT INTO #ids VALUES(1, 'Code1,Code2,Code3')

INSERT INTO #ids VALUES(2, 'Code1,Code2')

INSERT INTO #ids VALUES(3, 'Code3')

INSERT INTO #codes VALUES('Code1', 'Description1'), ('Code2', 'Description2'), ('Code3', 'Description3'), ('Code4', 'Description4')

SELECT id, LEFT(comb_codes.COL, LEN(comb_codes.COL) -1), LEFT(comb_desc.COL, LEN(comb_desc.COL) -1 ) FROM #ids CROSS APPLY (SELECT (SELECT code + ',' FROM #codes WHERE CHARINDEX(#codes.code, #ids.codes) > 0 FOR XML PATH('')) AS COL) comb_codes

CROSS APPLY (SELECT (SELECT descript + ',' FROM #codes WHERE CHARINDEX(#codes.code, #ids.codes) > 0 FOR XML PATH('')) AS COL) comb_desc

1Code1,Code2,Code3Description1,Description2,Description3

2Code1,Code2Description1,Description2

3Code3Description3

mahler9 Mr or Mrs. 500 Points: 569 More actions · Answer 3

mahler9

Mr or Mrs. 500

Points: 569

April 22, 2015 at 5:16 pm

#1792883

Thanks for responding, appreciate it.

mahler9 Mr or Mrs. 500 Points: 569 More actions · Answer 4

Thanks for responding to my post. I'll check out your suggestion, appreciate your time.

mahler9 Mr or Mrs. 500 Points: 569 More actions · Answer 5

Thanks for the response, CELKO.

ID is a primary key in "Table1". It has (I know, I know) a one-to-one relationship with the ID in a "Persons" table. Therefore, every person who has selected multiple "CODES" within the application has a single record in this table. Hope that clarifies ... a bit. I'd love nothing more than an individual record for every "CODE" per "ID". But it is, I'm fairly certain, an out of the box table for a proprietary software package. Fairly certain as I am new to this application's database structure.

You're preaching to the choir. As a data retriever happily chasing sticks it is ... like master throwing a bunch of sticks at the same time.

ScottPletcher SSC Guru Points: 100947 More actions · Answer 6

I believe the code needs slight corrections to prevent a match on part of the code value rather than whole value, as demonstrated below:

CREATE TABLE #ids(id int, codes varchar(30))

CREATE TABLE #codes(code varchar(30), descript varchar(30))

INSERT INTO #ids VALUES(1, 'Code1,Code2,Code3')

INSERT INTO #ids VALUES(2, 'Code1,Code2')

INSERT INTO #ids VALUES(3, 'Code3')

INSERT INTO #ids VALUES(11, 'Code11')

INSERT INTO #codes VALUES('Code1', 'Description1'), ('Code2', 'Description2'), ('Code3', 'Description3'), ('Code4', 'Description4')

,('Code11', 'Description11')

SELECT id, LEFT(comb_codes.COL, LEN(comb_codes.COL) -1), LEFT(comb_desc.COL, LEN(comb_desc.COL) -1 )

FROM #ids

CROSS APPLY (SELECT (SELECT code + ',' FROM #codes

WHERE CHARINDEX(#codes.code + ',', #ids.codes + ',') > 0 FOR XML PATH('')) AS COL) comb_codes

CROSS APPLY (SELECT (SELECT descript + ',' FROM #codes

WHERE CHARINDEX(#codes.code + ',', #ids.codes + ',') > 0 FOR XML PATH('')) AS COL) comb_desc

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

mahler9 Mr or Mrs. 500 Points: 569 More actions · Answer 7

SSCrazy, you are correct. Without the correction CHARINDEX was finding and returning two code values for for "Literacy"--"IT" and "Literacy".

Also, I discovered FOR XMLPATH doesn't like strings containing an ampersand. The description "This & That" is returned as "This &[amp]". I've added brackets to amp to make it appear in this post, not sure which IFCode would fix that.

One row to rule all them codes ... bad, bad, bad.