How to shift the value to the next row in SQL Server?

Question

I have a table input like

column1 | column2 
--------+--------
1,2,3   | A,B,C
4,5,6   | D,E,F

I need output like this:

column1 |column2
--------+-------
  1     | A
  2     | B
  3     | C 
  4     | D
  5     | E
  6     | F

Possible duplicate of [SQL Server split CSV into multiple rows](http://stackoverflow.com/questions/9811161/sql-server-split-csv-into-multiple-rows) — Tim Biegeleisen, Mar 20 '17 at 08:53
Possible duplicate of [Print SQL results on separate lines](http://stackoverflow.com/questions/22820038/print-sql-results-on-separate-lines) — Ramesh-X, Mar 20 '17 at 08:59
Are there always exactly three values in each column corresponding to exactly three values in the second column (1->a, 2->b and so on...)? — Shnugo, Mar 20 '17 at 09:03

score 0 · Answer 1 · answered Mar 20 '17 at 09:12

You question is quite unclear, but my magic crystal ball tells me, that you might be looking for something like this:

DECLARE @tbl TABLE(ID INT IDENTITY,column1 VARCHAR(100),column2 VARCHAR(100))
INSERT INTO @tbl VALUES
 ('1,2,3','A,B,C')
,('4,5,6','D,E,F');

WITH Casted AS
(
    SELECT ID
          ,CAST('<x>' + REPLACE(column1,',','</x><x>')+'</x>' AS XML) AS col1XML
          ,CAST('<x>' + REPLACE(column2,',','</x><x>')+'</x>' AS XML) AS col2XML
    FROM @tbl
)
SELECT ID,col1XML.value('/x[1]','int') AS Column1,col2XML.value('/x[1]','nvarchar(max)') AS Column2
FROM Casted 
UNION ALL
SELECT ID,col1XML.value('/x[2]','int') AS Column1,col2XML.value('/x[2]','nvarchar(max)') AS Column2
FROM Casted 
UNION ALL
SELECT ID,col1XML.value('/x[3]','int') AS Column1,col2XML.value('/x[3]','nvarchar(max)') AS Column2
FROM Casted 
ORDER BY ID,Column1

The result

Thanks @Shnugo this what I am looking for – Prk'sRules Mar 20 '17 at 11:19 — Prk'sRules, Mar 20 '17 at 11:19

How to shift the value to the next row in SQL Server?

1 Answers1