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,30Output:[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 30Help 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 cteXMLAS( 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 cteXMLAS( 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 cteXMLAS( 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 T1INNER JOIN #Temp2 AS T2ON T1.Rn = T2.RnINNER JOIN #Temp3 AS T3ON T2.Rn= T3.Rn--------------------------------------Drop tables ---------------------------------------------------------------------------------------DROP TABLE #Temp1DROP TABLE #Temp2DROP TABLE #Temp3----------------------------------------------------------------------------------------------------------------------------------------- ---------------Murali KrishnaYou live only once ..If you do it right once is enough....... |
|
|
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 #TableAVALUES (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 Split1AS( SELECT T.ID, S.ItemNumber, S.Item AS [Var] FROM #TableA T CROSS APPLY dbo.DelimitedSplit8K(T.[Var], ',') S),Split2AS( SELECT T.ID, S.ItemNumber, S.Item AS Vol FROM #TableA T CROSS APPLY dbo.DelimitedSplit8K(T.Vol, ',') S),Split3AS( 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.ValFROM 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; |
|
|
|
|
|