'Duplicate and Split Row based on value by using SQL Query?

I have set of data and wants to split multiple rows based on the column value.

For Example,

Source Data:

enter image description here

Expected Output:

enter image description here

Thanks, Lawrance A

sqlsql-serversplit


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

Related Questions