Need to concatenate a description field

Transact-SQL
1

I need to concatenate a description field from one table from a field from another table. That field contains multiple id's separated by commas.

I'm at a loss as to how to accomplish this without creating several temp tables of the matching data. The result set will contain several hundred records so i think a cursor would be too time intensive.

Does anyone have any suggestions?

2
  1. post your table definitions (CREATE TABLE statements)
  2. Post INSERT INTO statements to populate the tables
  3. Post the expected results using the tables and data from steps 1 and 2
3

Here's the TABLE Definitions, INSERT Statements, and Expected Results:
NOTE: These tables originate from a PROGRESS database that are accessible from a linked server utilizing the OPENQUERY function, so it appears that there are no identity fields.

*** CREATE ***

CREATE TABLE dbo.WOAudit(
WONumber varchar(26) NULL,
MachPCode varchar(50) NULL,
MachSCode varchar(100) NULL
)

CREATE TABLE pub.MachinePCodes(
MachPCode varchar(6) NULL,
Description varchar(100) NULL
)

CREATE TABLE pub.MachineSCodes(
MachSCode varchar(6) NULL,
MachPCode varchar(6) NULL,
Description varchar(100) NULL
)

*** INSERT ***

INSERT INTO pub.MachinePCodes
VALUES('21','Quality'),
('33','Wrapping')

INSERT INTO pub.MachineSCodes
VALUES('21','330','Other'),
('21','306','Bad Bond'),
('33','306','Cold Wrap'),
('33','330','Warm Wrap')

INSERT INTO pub.WOAudit
VALUES('70412053600','21','330'),
('70412053601','33','306'),
('70412053602','33','306,330'),
('70412053603','33','330')

*** Expected Results ***

WONumber MachPCode PDesc MachSCode SDesc
70412053600 21 Quality 330 Other
70412053601 33 Wrapping 306 Bad Bond
70412053602 33 Wrapping 306,330 Cold Wrap, Warm Wrap
70412053603 33 Wrapping 330 Warm Wrap

4

I'm confused by your data. table MachinePCodes gets entries for pcodes 21 and 33, but table MachineSCodes gets pcodes for 330 and 306. Shouldn't the be the same?

5

Ah - I mixed up the order of the input data. The 2-digit number is the PCode or PrimaryCode and the 3-digit number is the SCode or SecondaryCode. My Apologies.

6

Try this:

SELECT wo.WONumber
      , wo.MachPCode
      , Pcodes.[Description] AS Pdesc
      , stuff(scodes, 1, 1, '') AS MachSCode
      , STUFF(sdesc, 1, 1, '') AS SDesc
FROM #dbo_WOAudit WO
INNER JOIN #pub_MachinePCodes pcodes
      ON wo.MachPCode = pcodes.MachPCode
CROSS APPLY (
      SELECT ',' + scodes.MachSCode
      FROM #pub_MachineSCodes scodes
      WHERE scodes.MachPCode = wo.MachPCode
            AND 0 < charindex(',' + scodes.MachSCode + ',', ',' + wo.MachSCode + ',')
      ORDER BY scodes.MachSCode
      FOR XML path('')
      ) _(scodes)
CROSS APPLY (
      SELECT ',' + scodes.Description
      FROM #pub_MachineSCodes scodes
      WHERE scodes.MachPCode = wo.MachPCode
            AND 0 < charindex(',' + scodes.MachSCode + ',', ',' + wo.MachSCode + ',')
      ORDER BY scodes.MachSCode
      FOR XML path('')
      ) __(SDesc)
7

Worked like a charm! I made a few adjustments to separate out those records that do not have a numeric secondaryCode, load them first into a temp table, and then get all the other records that have a numeric secondaryCode and load them into that temp table. Then I just select * from that temp table. I guess I have a bit more to learn about SQL because I have never seen CROSS APPLY. Brilliant 'gbritton'!! Thank you.

Here's how I changed things:

First....
INSERT INTO #tmpTblSummary
( WONumber
, PrimaryCode
, PrimaryDescription
, SecondaryCode
, SecondaryDescription
)
SELECT wo.WONumber
, wo.PrimaryCode
, Pcodes.PrimaryDescription
, wo.SecondaryCode
, ISNULL(STUFF(sdesc, 1, 1, ''),'None') AS SecondaryDescription
FROM pub.TblMainRecords WO
INNER JOIN pub.TblPrimaryCodes pcodes
ON wo.PrimaryCode = pcodes.MachPCode
CROSS APPLY (
SELECT ',' + scodes.MachSCode
FROM pub.TblSecondaryCodes scodes
WHERE scodes.MachPCode = wo.PrimaryCode
AND (0 < charindex(',' + scodes.MachSCode + ',', ',' + wo.SecondaryCode + ','))
ORDER BY scodes.MachSCode
FOR XML path('')
) _(scodes)
CROSS APPLY (
SELECT ',' + scodes.SecondaryDescription
FROM pub.TblSecondaryCodes scodes
WHERE scodes.MachPCode = wo.PrimaryCode
AND 0 < charindex(',' + scodes.MachSCode + ',', ',' + wo.SecondaryCode + ',')
ORDER BY scodes.MachSCode
FOR XML path('')
) __(SDesc)
WHERE CHARINDEX(',',wo.SecondaryCode) > 0

Then...
INSERT INTO #tmpTblSummary
( WONumber
, PrimaryCode
, PrimaryDescription
, SecondaryCode
, SecondaryDescription
)
SELECT wo.WONumber
, wo.PrimaryCode
, Pcodes.PrimaryDescription
, wo.SecondaryCode
, ISNULL(scodes.SecondaryDescription,'None') As SecondaryDescription
FROM pub.TblMainRecords wo
LEFT JOIN pub.TblPrimaryCodes pcodes
ON pcodes.MachPCode = wo.PrimaryCode
LEFT JOIN pub.TblSecondaryCodes scodes
ON scodes.MachPCode = wo.PrimaryCode
AND scodes.MachSCode = wo.SecondaryCode
WHERE CHARINDEX(',',wo.SecondaryCode) = 0

SELECT *
FROM #tmpTblSummary
ORDER BY WONumber, PrimaryCode, SecondaryCode

8

By-the-way please use the </> icon to format your code. (Between the quotes and upload)