I have 2 tables:
Table1
ID Name
------------
1 Cpu
2 Vga
3 Ram
Table2 :
ID Names
-------------
1 1;2
2 1;2;3
3 2
I want result: Select in table 2 :
ID Names Names string
----------------------------
1 1;2 Cpu,Vga
2 1;2;3 Cpu,Vga,Ram
3 2 Vga
How to solve my problem? Thank you
;WITH cte AS (
SELECT * FROM Table1 t1
INNER JOIN (
SELECT id AS t2_id, VALUE AS nameid
FROM Table2 t2
CROSS APPLY STRING_SPLIT(Names,';')
) t3
ON t1.id=t3.nameid
)
SELECT id ,
STUFF((SELECT ','+ Name FROM cte c2 where c.id =c2.t2_id FOR XML PATH('')),1,1,'' )
AS [Name String],
STUFF ((SELECT ','+ cast(t2_id as varchar) from cte c2
WHERE c.id=c2.nameid FOR XML PATH('')),1,1,'' ) AS Names
FROM cte c
GROUP BY id
OUTPUT
id Name String Names
1 Cpu,Vga 1,2
2 Cpu,Vga,Ram 1,2,3
3 Vga 2
NOTE
As @plaidDK pointed out in comments that,
STRING_SPLIT() is not a function of SQL SERVER 2008, the above query will help if you find some Stored procedures to do the functionality of STRING_SPLIT() .
And Substitute STRING_SPLIT() with Stored procedure name
This here will do the trick for you.
You need to create a function which can split out your rows so you can join them afterwards and use som XML to concat your rows back.
You need to create a function which can split your rows or unconcat
CREATE FUNCTION [dbo].[dba_parseString_udf] (
@stringToParse VARCHAR(8000)
, @delimiter CHAR(1)
)
RETURNS @parsedString TABLE (stringValue VARCHAR(128)) AS
BEGIN
/* Declare variables */
DECLARE @trimmedString VARCHAR(8000);
/* We need to trim our string input in case the user entered extra spaces */
SET @trimmedString = LTRIM(RTRIM(@stringToParse));
/* Let's create a recursive CTE to break down our string for us */
WITH parseCTE (StartPos, EndPos)
AS
(
SELECT 1 AS StartPos
, CHARINDEX(@delimiter, @trimmedString + @delimiter) AS EndPos
UNION ALL
SELECT EndPos + 1 AS StartPos
, CHARINDEX(@delimiter, @trimmedString + @delimiter , EndPos + 1) AS EndPos
FROM parseCTE
WHERE CHARINDEX(@delimiter, @trimmedString + @delimiter, EndPos + 1) <> 0
)
/* Let's take the results and stick it in a table */
INSERT INTO @parsedString
SELECT SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)
FROM parseCTE
WHERE LEN(LTRIM(RTRIM(SUBSTRING(@trimmedString, StartPos, EndPos - StartPos)))) > 0
OPTION (MaxRecursion 8000);
RETURN;
END
Inner table here is your Table2 and split table is your table1
SQL Code to run
with result as (
SELECT [id]
,[name]
,i.stringValue
from [LegOgSpass].[dbo].[inner] as a
cross apply [dbo].[dba_parseString_udf](a.Name,';') i
)
,partresult as (
select a.id,a.name,b.name as RowstoConcat from result a
left join LegOgSpass.dbo.split b on a.stringValue = b.id
)
SELECT id,Name, Pets = STUFF((SELECT N', ' + RowstoConcat
FROM partresult AS p2
WHERE p2.name = p.name
ORDER BY RowstoConcat
FOR XML PATH(N'')), 1, 2, N'')
FROM partresult AS p
GROUP BY id,Name
ORDER BY id,Name
Result
