'Duplicate and Split Row based on value by using SQL Query?
Solution 1:[1]
The Best and Simple way to solve above is
SELECT value AS UID,
NAME,
Age,
Education,
Department
FROM StackSoln
CROSS apply String_split(UID, ',')
STRING_SPLIT
A table-valued function that splits a string into rows of substrings, based on a specified separator character.
CROSS APPLY
returns only rows from the outer table that produce a result set from the table-valued function. It other words, result of CROSS APPLY doesn't contain any row of left side table expression for which no result is obtained from right side table expression. CROSS APPLY work as a row by row INNER JOIN
Solution 2:[2]
Assuming sql-server
is greater or equal than 2016
version. Then, string_split()
function can be used:
select spl.u_id, s.name us_uid, s.age col_1, s.dob col_2, s.education col_3, s.department col_4 from @source s
cross apply( select value u_id from string_split( (select u_id from @source s2 where s2.u_id = s.u_id ),',')
where CHARINDEX(value,s.u_id,1) > 0) spl
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
Solution | Source |
---|---|
Solution 1 | RF1991 |
Solution 2 | SNR |