How to Convert row string value into column by using SQL query

How to achieve below requirement in SQL Server.

Data what I have:

Table A(Input

and Expected output is:

Table B(Expected Output

Thanks, Lawrance A

728x90

2 Answers How to Convert row string value into column by using SQL query

Assuming of your given data value:

 'Message:"A",Level:"0",type:"log"'
 'Message:"B",Level:"1",type:"log"'

select substring(ColumnA,10,1) Message,
replace(dbo.udf_GetNumeric(replace(replace(substring(ColumnA,20,5),'"',''),',','')),' ','')[type],
replace(replace(substring(ColumnA,29,250),'"',''),',','')[Log] from YourTable

GetNumeric here

4 months ago

I have created two function to achieve your desired output. use below query by creating two function split and getVal

select 
    dbo.getVal(columnA,'Message') Message,
    dbo.getVal(columnA,'Level') [Level],
    dbo.getVal(columnA,'type') [type]
from TableA

Query for creating split and getVal function

Create function split(@s varchar(500),@splitWith varchar(20))
returns @RetTable Table(id int identity(1,1), Value varchar(200))
as
begin
    if(CHARINDEX(@splitWith,@s)>0)
    begin
    set @[email protected][email protected]
        declare @len int =len(@s)
        while charindex(@splitWith,@s)>0
        begin
            insert @RetTable values(SUBSTRING(@s,1,charindex(@splitWith,@s)-1))
            set @s=SUBSTRING(@s,charindex(@splitWith,@s,1)+1,@len)
        end
    end
    return
end



 Create function getVal(@str varchar(500),@column varchar(200))
    returns varchar(200)
    as
    begin
    declare @ret varchar(200)
        select @ret=value From dbo.split((select value From dbo.split('Message:"A",Level:"0",type:"log"',',') where value like [email protected]+'%'),':') where id=2
        return replace(@ret,'"','')
    end

4 months ago