CODEWITHSUNDEEP
sqlsql-serversql-server-2012
Mayur Randive
Mayur Randive

Reputation: 643

Separate cells content into rows in SQL Server

Here is a requirement where I want to separate out content from a cell, Can someone guide me on the possibility and approach to achieve it.

Source Table : 

ID | Log
____________________
1  | Status : New 
   | Assignment : 1
   | Priority : Low
_____________________
2  | Status : In Progress
   | Impact : High

Target Table : 

ID | Key       | Value
____________________
1  | Status    | New 
1  | Assignment| 1
1  | Priority  | Low
2  | Status    | In Progress
2  | Impact    | High

Upvotes: 1

Views: 62

Answers (2)

Eralper
Eralper

Reputation: 6622

You can use following SQL script too, But before using it you need to create a SQL split function on your database. I'm using one of the referred split functions

with tbl as (
    select 
        ID, Log, row_number() over (order by Log) as rn
    from sourceTable
)
select
    ID,
    max([Key]) as [Key],
    max([Value]) as [Value]
from (
    select
        ID,
        rn,
        case when sid = 1 then val else null end as [Key],
        case when sid = 2 then val else null end as [Value]
    from (
        select 
            tbl.ID, 
            tbl.rn, 
            s.id sid,
            val
        from tbl
        cross apply dbo.split(tbl.Log,':') s
    ) t
) t
group by ID, rn
order by ID, rn

Output is as follows

enter image description here

Upvotes: 1

John Cappelletti
John Cappelletti

Reputation: 82010

Assuming [Log] is multi-line delimited with CRLF

Example

Select A.ID
      ,[Key]   = ltrim(rtrim(left(RetVal,charindex(':',RetVal)-1)))
      ,[Value] = ltrim(rtrim(substring(RetVal,charindex(':',RetVal)+1,len(RetVal))))
 From  YourTable A
 Cross Apply (
                Select RetSeq = Row_Number() over (Order By (Select null))
                      ,RetVal = LTrim(RTrim(B.i.value('(./text())[1]', 'varchar(max)')))
                From  (Select x = Cast('<x>' + replace((Select replace(A.[log],char(10),'§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as B1
                Cross Apply x.nodes('x') AS B(i)
             ) B

Returns

ID  Key         Value
1   Status      New
1   Assignment  1
1   Priority    Low
2   Status      In Progress
2   Impact      High

Upvotes: 2

Related Questions