Split column values into rows and preserve value from second column

Question

I'm having troubles with one of our tables, which i intend to use in viz tools. Maybe you could guide me on how to achieve below.

My table looks like this:

123 | ABC DEF
456 | GHI JKL MNO
789 | PRS

And I would like to split second column into rows and preserve value from first column:

123 | ABC
123 | DEF
456 | GHI
456 | JKL 
456 | MNO
789 | PRS

Could you tell me how to achieve that? Thanks in advance. Cheers!

What version of SQL Server? There are some built-in functions in the latest version (and Azure SQL DB) that handle this. — Vinnie, Nov 09 '17 at 12:40
Possible duplicate of [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) — Valerica, Nov 09 '17 at 12:49
Never, ever store data like that. It will only cause you lots of trouble. — jarlh, Nov 09 '17 at 12:51

score 2 · Accepted Answer · answered Nov 09 '17 at 12:42

2

You could use XML node method to split the data :

SELECT ID, split.a.value('.', 'VARCHAR(MAX)') DATA
FROM
(
   SELECT ID, CAST('<A>'+REPLACE(DATA, ' ','</A><A>')+'</A>' AS XML) AS data from <table_name>
) a
CROSS APPLY data.nodes('/A') AS split(a);

Result :

ID  DATA
123 ABC
123 DEF
456 GHI
456 JKL
456 MNO
789 PRS

answered Nov 09 '17 at 12:42

Yogesh Sharma

49,870
5
26
52

Works just fine. Thank you. – saif3r Nov 09 '17 at 12:56

Split column values into rows and preserve value from second column

1 Answers1