CODEWITHSUNDEEP
sqldatabaset-sqlsql-server-2008
Narendra
Narendra

Reputation: 345

Split one cell into multiple rows in SQL Server

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')

enter image description here

Upvotes: 0

Views: 1629

Answers (2)

Serg
Serg

Reputation: 22811

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)

Upvotes: 3

Stu
Stu

Reputation: 32614

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])

Upvotes: 4

Related Questions