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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Comma Separated Columns into Rows

Author  Topic 

Kalaiselvan
Posting Yak Master

112 Posts

Posted - 2014-05-20 : 08:11:14
Hi,
Got a table with Some Column contains Comma Separated Values. NEed to
Convert the Comma separated Column values into Rows.

Ex: TABLEA
[ID] [VAR] [VOL] [VAL]
1 V1,V2,V3 25,75,45 50,60,80
2 V1,V2 100,90 70,60
3 V1,V2,V3,V4 15,30,80,90 90,60,45,30

Output:
[ID] [VAR] [VOL] [VAL]
1 V1 25 50
1 V2 75 60
1 V3 45 80
2 V1 100 70
2 V2 90 60
3 V1 15 90
3 V2 30 60
3 V2 80 45
3 V2 90 30


Help me to fix this with the Query.

Regards,
Kalai

MuralikrishnaVeera
Posting Yak Master

129 Posts

Posted - 2014-05-20 : 09:04:49
Got it ......Here it is.


---------------------------------------Create your Test Table ----------------------------------------------------------------------------
CREATE TABLE Temp([ID] INT,[VAR] VARCHAR(MAX),[VOL] VARCHAR(0124),[VAL] VARCHAR(1024))
INSERT INTO Temp VALUES(1 ,'V1,V2,V3','25,75,45','50,60,80'),(2,'V1,V2','100,90','70,60'),(3,'V1,V2,V3,V4','15,30,80,90','90,60,45,30')
--SELECT * FROM Temp

--------------------------------------Converting [var] values to columns -----------------------------------------------------------------
CREATE TABLE #Temp1(ID INT,[VAR] VARCHAR(1024),Rn INT)
;WITH cteXML
AS( SELECT ID
,CAST('<r>' + REPLACE([VAR],',','</r><r>')+'</r>' AS XML) AS parents
FROM Temp
)
INSERT INTO #Temp1 (ID,[VAR],Rn)
SELECT ID
,x.value('.','VARCHAR(100)') AS [var]
,ROW_NUMBER()OVER (ORDER BY (SELECT 1)) AS Rn
FROM cteXML t1 CROSS APPLY parents.nodes('r') a(x)
--SELECT * FROM #Temp1

--------------------------------------Converting [vol] values to columns -----------------------------------------------------------------
CREATE TABLE #Temp2([VOL] VARCHAR(1024),Rn INT)
;WITH cteXML
AS( SELECT
CAST('<r>' + REPLACE([VOL],',','</r><r>')+'</r>' AS XML) AS parents
FROM Temp
)
INSERT INTO #Temp2 ([VOL],Rn)
SELECT
x.value('.','VARCHAR(100)') AS [VOL]
,ROW_NUMBER()OVER (ORDER BY (SELECT 1)) AS Rn
FROM cteXML t1 CROSS APPLY parents.nodes('r') a(x)
--SELECT * FROM #Temp2
--------------------------------------Converting [vol] values to columns -----------------------------------------------------------------
CREATE TABLE #Temp3([VAL] VARCHAR(1024),Rn INT)
;WITH cteXML
AS( SELECT
CAST('<r>' + REPLACE([VAL],',','</r><r>')+'</r>' AS XML) AS parents
FROM Temp
)
INSERT INTO #Temp3 ([VAL],Rn)
SELECT
x.value('.','VARCHAR(100)') AS [VAL]
,ROW_NUMBER()OVER (ORDER BY (SELECT 1)) AS Rn
FROM cteXML t1 CROSS APPLY parents.nodes('r') a(x)

--------------------------------------Desired result set ---------------------------------------------------------------------------------------
SELECT T1.ID
,T1.[VAR]
,T2.[VOL]
,T3.[VAL]
FROM #Temp1 AS T1
INNER JOIN #Temp2 AS T2
ON T1.Rn = T2.Rn
INNER JOIN #Temp3 AS T3
ON T2.Rn= T3.Rn

--------------------------------------Drop tables ---------------------------------------------------------------------------------------

DROP TABLE #Temp1
DROP TABLE #Temp2
DROP TABLE #Temp3
-----------------------------------------------------------------------------------------------------------------------------------------





---------------
Murali Krishna

You live only once ..If you do it right once is enough.......
Go to Top of Page

Ifor
Aged Yak Warrior

700 Posts

Posted - 2014-05-20 : 10:42:27
It will probably be quicker, and easier to maintain, to take the string splitting function from here:

http://www.sqlservercentral.com/articles/Tally+Table/72993/

Then the following should work:

-- *** Test Data ***
CREATE TABLE #TableA
(
ID int NOT NULL
,[Var] varchar(255) NOT NULL
,Vol varchar(255) NOT NULL
,Val varchar(255) NOT NULL
);
INSERT INTO #TableA
VALUES (1, 'V1,V2,V3', '25,75,45', '50,60,80')
,(2, 'V1,V2', '100,90', '70,60')
,(3, 'V1,V2,V3,V4', '15,30,80,90', '90,60,45,30');
-- *** End Test Data ***

WITH Split1
AS
(
SELECT T.ID, S.ItemNumber, S.Item AS [Var]
FROM #TableA T
CROSS APPLY dbo.DelimitedSplit8K(T.[Var], ',') S
)
,Split2
AS
(
SELECT T.ID, S.ItemNumber, S.Item AS Vol
FROM #TableA T
CROSS APPLY dbo.DelimitedSplit8K(T.Vol, ',') S
)
,Split3
AS
(
SELECT T.ID, S.ItemNumber, S.Item AS Val
FROM #TableA T
CROSS APPLY dbo.DelimitedSplit8K(T.Val, ',') S
)
SELECT S1.ID, S1.[Var], S2.Vol, S3.Val
FROM Split1 S1
JOIN Split2 S2
ON S1.ID = S2.ID
AND S1.ItemNumber = S2.ItemNumber
JOIN Split3 S3
ON S1.ID = S3.ID
AND S1.ItemNumber = S3.ItemNumber;
Go to Top of Page
   

- Advertisement -