Split one cell into multiple rows in SQL Server-CodePudding

Split single cell value into multiple rows by duplicating the id column and using only portion of the original text. Any way other than using UNION.

Here is the sample data

create table Spl 
(
     id INT, 
     Name VARCHAR(100)
)

insert into Spl values (1, '4334ASTBSTCST')
insert into Spl values (2, '7887ASTBSTCST')
insert into Spl values (3, '8793ASTBSTCST')

CodePudding user response：

You can use cross apply with values:

select Id, v.[Name] 
from spl
cross apply (
    values
    (Left([name],7)),
    (Left([name],4)   Substring([name],8,3)),
    (Left([name],4)   Substring([name],11,3))
)v([Name])

CodePudding user response：

A version of cross apply

select Id, left([name],4)   substring([name], v.pos, v.len)
from spl
cross apply (
    values
    ( 5,3),
    ( 8,3),
    (11,3)
) v(pos,len)

CodePudding user response：

Use UNION:

WITH u
 AS (SELECT id,
            Substring(NAME, 1, 4)   Substring(NAME, 5, 3) AS ss1
     FROM   spl),
 v
 AS (SELECT id,
            Substring(NAME, 1, 4)   Substring(NAME, 8, 3) AS ss2
     FROM   spl),
 w
 AS (SELECT id,
            Substring(NAME, 1, 4)   Substring(NAME, 11, 3) AS ss3
     FROM   spl)
(SELECT * FROM u UNION SELECT * FROM v UNION SELECT * FROM w)