Concatenating strings into one row
-
Hi all,
I have a table with ten thousands of rows that looks something like this:
CREATE TABLE #temp
(
company VARCHAR(50) NOT NULL,
details VARCHAR (200) NULL ,
details2 VARCHAR (200) NULL ,
code VARCHAR (50) NULL
)
INSERT INTO #temp VALUES( 'td tech', NULL, NULL, '0321')
INSERT INTO #temp VALUES( 'td tech', NULL, NULL, '0654')
INSERT INTO #temp VALUES( 'td tech', NULL, NULL, '0987')
INSERT INTO #temp VALUES( 'td tech', 'publishing', 'PUL12', NULL)
INSERT INTO #temp VALUES( 'td tech', 'publishing', 'PUG58', NULL)
INSERT INTO #temp VALUES( 'td tech', 'publishing', 'PUJ51', NULL)
INSERT INTO #temp VALUES( 'td tech', 'pharma', 'PH582', NULL)
INSERT INTO #temp VALUES( 'td tech', 'pharma', 'PH520', NULL)
INSERT INTO #temp VALUES( 'td tech', 'pharma', 'PH008', NULL)
INSERT INTO #temp VALUES( 'op tech', NULL, NULL, '9804')
INSERT INTO #temp VALUES( 'op tech', NULL, NULL, '2356')
INSERT INTO #temp VALUES( 'op tech', NULL, NULL, '0070')
INSERT INTO #temp VALUES( 'op tech', 'publishing', 'PUV15', NULL)
INSERT INTO #temp VALUES( 'op tech', 'publishing', 'PUT92', NULL)
INSERT INTO #temp VALUES( 'op tech', 'publishing', 'PUY30', NULL)
I would like to see one row only for each company in the following format:
company code-merged str-merged
td tech 0321, 0654, 0987 publishing: PUL12, PUG58, PUJ51, pharma:PH582, PH520, PH008
op tech 9804, 2356, 0070 publishing: PUV15, PUT92, PUY30
I'm trying to merge the strings into one row using for xml path eg.
SELECT code + ', ' FROM #temp WHERE company = 'td tech' AND details IS null for xml path('')
This does the first only. Any way to do this on the entire table dynamically?
thanks
__________________________
Allzu viel ist ungesund... -
Maybe this?
WITH CTE AS (SELECT company,details,details2,
ROW_NUMBER() OVER(PARTITION BY company,details
ORDER BY details2) AS rn
FROM #temp
WHERE details IS NOT NULL)
SELECT a.company,
STUFF((SELECT ', ' + b.code AS "text()"
FROM #temp b
WHERE b.company=a.company
AND b.code IS NOT NULL
ORDER BY b.code
FOR XML PATH('')),1,2,'') AS [code-merged],
(SELECT CASE WHEN c.rn=1 THEN ' ' + c.details+': ' ELSE ', ' END +
c.details2 AS "text()"
FROM CTE c
WHERE c.company=a.company
ORDER BY c.details2
FOR XML PATH('')) AS [str-merged]
FROM #temp a
GROUP BY a.company;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Here's a couple of options: -
BEGIN TRANCREATE TABLE #temp(
company VARCHAR(50) NOT NULL,
details VARCHAR (200) NULL ,
details2 VARCHAR (200) NULL ,
code VARCHAR (50) NULL)
INSERT INTO #temp VALUES( 'td tech', NULL, NULL, '0321')
INSERT INTO #temp VALUES( 'td tech', NULL, NULL, '0654')
INSERT INTO #temp VALUES( 'td tech', NULL, NULL, '0987')
INSERT INTO #temp VALUES( 'td tech', 'publishing', 'PUL12', NULL)
INSERT INTO #temp VALUES( 'td tech', 'publishing', 'PUG58', NULL)
INSERT INTO #temp VALUES( 'td tech', 'publishing', 'PUJ51', NULL)
INSERT INTO #temp VALUES( 'td tech', 'pharma', 'PH582', NULL)
INSERT INTO #temp VALUES( 'td tech', 'pharma', 'PH520', NULL)
INSERT INTO #temp VALUES( 'td tech', 'pharma', 'PH008', NULL)
INSERT INTO #temp VALUES( 'op tech', NULL, NULL, '9804')
INSERT INTO #temp VALUES( 'op tech', NULL, NULL, '2356')
INSERT INTO #temp VALUES( 'op tech', NULL, NULL, '0070')
INSERT INTO #temp VALUES( 'op tech', 'publishing', 'PUV15', NULL)
INSERT INTO #temp VALUES( 'op tech', 'publishing', 'PUT92', NULL)
INSERT INTO #temp VALUES( 'op tech', 'publishing', 'PUY30', NULL)
--Option 1
SELECT company,
COALESCE(STUFF((SELECT ', ' + code
FROM #temp t2
WHERE t2.company = t1.company AND t2.details IS NULL
FOR XML PATH('')), 1, 2, ''),'') +
COALESCE(' publishing: ' + STUFF((SELECT ', ' + details2
FROM #temp t3
WHERE t3.company = t1.company AND t3.details = 'publishing'
FOR XML PATH('')), 1, 2, ''),'') +
COALESCE(' pharma: ' + STUFF((SELECT ', ' + details2
FROM #temp t4
WHERE t4.company = t1.company AND t4.details = 'pharma'
FOR XML PATH('')), 1, 2, ''),'') AS ConcatenatedString
FROM #temp t1
GROUP BY company
--Option 2
SELECT company,
(SELECT grpCodes.grpCode
FROM (SELECT ', ' + code
FROM #temp t2
WHERE t2.company = t1.company AND t2.details IS NULL
FOR XML PATH(''), TYPE) codes(code)
CROSS APPLY (SELECT STUFF(codes.code.value('./text()[1]', 'VARCHAR(MAX)'),1,2,'')) grpCodes(grpCode)) +
COALESCE(' publishing: ' + (SELECT grpDetails2.grpDetail2
FROM (SELECT ', ' + details2
FROM #temp t3
WHERE t3.company = t1.company AND t3.details = 'publishing'
FOR XML PATH(''), TYPE) details2(detail2)
CROSS APPLY (SELECT STUFF(details2.detail2.value('./text()[1]', 'VARCHAR(MAX)'),1,2,'')) grpDetails2(grpDetail2)),'') +
COALESCE(' pharma: ' + (SELECT grpDetails2.grpDetail2
FROM (SELECT ', ' + details2
FROM #temp t4
WHERE t4.company = t1.company AND t4.details = 'pharma'
FOR XML PATH(''), TYPE) details2(detail2)
CROSS APPLY (SELECT STUFF(details2.detail2.value('./text()[1]', 'VARCHAR(MAX)'),1,2,'')) grpDetails2(grpDetail2)),'') AS ConcatenatedString
FROM #temp t1
GROUP BY company
ROLLBACK
Both return -
company ConcatenatedString-------------------------------------------------- -----------------------------------------------------------------------------
op tech 9804, 2356, 0070 publishing: PUV15, PUT92, PUY30
td tech 0321, 0654, 0987 publishing: PUL12, PUG58, PUJ51 pharma: PH582, PH520, PH008
--edit--Based on Mark's very clever script, option 3: -
SELECT company,(SELECT CASE WHEN t2.rn=1
THEN COALESCE(' ' + t2.details + ': ','') ELSE ', ' END + COALESCE(t2.details2, t2.code)
FROM (SELECT company, details2, details, code,
ROW_NUMBER() OVER(PARTITION BY company,details ORDER BY details2) AS rn
FROM #temp) t2
WHERE t2.company = t1.company
ORDER BY t2.details2
FOR XML PATH(''))
FROM #temp t1
GROUP BY company
Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/ -
Brilliant! Thanks for your time and have a good week!
__________________________
Allzu viel ist ungesund...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply