Sub select query Help

Hi,

Please check following item

It uses a SQL string function, please create it first

/*
create table Q2STATUS (Pid nvarchar(200), Status varchar(20), paid_amount int)
insert into Q2STATUS select '123456 = Original_PID, Reversal =123456R1, Adjustment= 123456A1',NULL,250
*/

with cte2 as (
        select rn = row_number() over (order by pid), * from Q2STATUS
), cte as (
        select cte2.*, s.id typeid, v.*
        from cte2
        cross apply dbo.split(cte2.Pid, ',') s
        cross apply dbo.split(s.val, '=') v
), cte3 as (
select
        rn, pid, status, paid_amount, max(pidtype) pidtype, max(pidval) pidval
from (
        select
                rn, Pid, status, paid_amount, typeid,
                case when (typeid = 1 and id = 2) or (typeid <> 1 and id = 1) then ltrim(rtrim(val)) else null end as pidtype,
                case when (typeid = 1 and id = 1) or (typeid <> 1 and id = 2) then ltrim(rtrim(val)) else null end as pidval
        from cte
) g
group by rn, pid, status, paid_amount, typeid
)
select
        cte2.rn, cte2.pid,
        cte3.pidval as 'Original_PID',
        cte4.pidval as 'Reversal',
        cte5.pidval as 'Adjustment'
from cte2
left join cte3 on cte3.rn = cte2.rn and pidtype = 'Original_PID'
left join cte3 as cte4 on cte4.rn = cte2.rn and cte4.pidtype = 'Reversal'
left join cte3 as cte5 on cte5.rn = cte2.rn and cte5.pidtype = 'Adjustment'

---

SQL Server, SQL Server 2012 Denali and T-SQL Tutorials