I have a table in SQL Server 2008 database, the table has two columns,as follow:
I want to select the data as following:
3
-
2stackoverflow.com/questions/2647/…Vitaly Borisov– Vitaly Borisov2019-07-28 23:21:21 +00:00Commented Jul 28, 2019 at 23:21
-
Best thing you could do is fix this so the table is properly normalized in the first place. Storing delimited data is columns is really bad. But I know that's not always possible.Joel Coehoorn– Joel Coehoorn2019-07-29 01:22:11 +00:00Commented Jul 29, 2019 at 1:22
-
Side note: SQL Server 2008 and 2008 R2 are out of extended support by now - red-gate.com/simple-talk/sql/database-administration/… - time to upgrade!marc_s– marc_s2019-07-29 04:46:22 +00:00Commented Jul 29, 2019 at 4:46
Add a comment
|
2 Answers
This type of operation is rather painful in SQL Server, because the built-in string functions are pretty bad. The referenced question uses a while loop -- which is unnecessary. You can construct this all in one query using a recursive CTE:
with t as (
select 'ali' as col1, 'A;B;C' as col2
),
cte as (
select col1,
convert(varchar(max), left(col2, charindex(';', col2) - 1)) as val,
convert(varchar(max), stuff(col2, 1, charindex(';', col2), '') + ';') as rest
from t
union all
select col1,
convert(varchar(max), left(rest, charindex(';', rest) - 1)) as val,
convert(varchar(max), stuff(rest, 1, charindex(';', rest), '')) as rest
from cte
where rest <> ''
)
select cte.*
from cte;
Comments
I had a similar situation and I solved it using XML querying with this as my guide. I am not super proficient with XML queries, so I am hesitant to share my answer because I cannot fully explain it line by line even though it does work. What I do understand is that you replace your separator character (or string) with closing and opening XML tags with a open tag at the very beginning and a close tag at the very end which transforms this...
A;B;C
into this...
<X>A</X>
<X>B</X>
<X>C</X>
You can use XML query syntax to retrieve each of those nodes. There is nothing magical about "X" other than you have to use the same tag in the nodes() method in CROSS APPLY section.
CREATE TABLE Table1
(
Column1 VARCHAR(20)
, Column2 VARCHAR(50)
);
INSERT INTO Table1 (Column1, Column2) VALUES ('Ali', 'A;B;C');
INSERT INTO Table1 (Column1, Column2) VALUES ('Ahmed', 'D;E');
DECLARE @Separator VARCHAR(10);
SET @Separator = ';';
SELECT a.Column1
, b.SplitData
FROM (
SELECT Column1
, CAST('<X>' + REPLACE((
SELECT Column2 AS [*] FOR XML PATH('')
)
, @Separator
, '</X><X>'
) + '</X>' AS XML) xmlfilter
FROM Table1
) AS a
CROSS APPLY (
SELECT LetterIDs.Column2.value('.', 'varchar(50)') AS SplitData
FROM a.xmlfilter.nodes('X') AS LetterIDs(Column2)
) AS b;
Here is the db fiddle. I hope this helps.
