quote:

---

Originally posted by TG

If you provide your source data as executable DDL/DML I'll provide a solution. But I don't feel like typing all that myself.
Otherwise here's one way to do it:
- create a CTE so that you can break out Label and Value into different columns (as they should be anyway)
- self join two instances of your table with a left outer join.
- join on index and label with one table instance type = 'P' and the other instance type = 'N'
- then just select what you want out of two instances of your table.

Be One with the Optimizer
TG

---

quote:

---

If you provide your source data as executable DDL/DML I'll provide a solution. But I don't feel like typing all that myself.

---

;with yourTable ([index], dateupdated, label, [type])
as
(
      select 124512, '2013-05-10', 'PA 1', 'P' union all
      select 124512, '2013-05-10', 'PA 2', 'N' union all
      select 124513, '2013-05-11', 'NJ 10', 'N' union all
      select 124514, '2013-05-12', 'NY 5', 'P' union all
      select 124515, '2013-05-15', 'MD 20', 'P' union all
      select 124515, '2013-05-15', 'MD 21', 'N' union all
      select 124515, '2013-05-15', 'CT 22', 'N' 
)

,cte ([index], dateupdated, label, value, [type])
as
(
       select [index]
              , dateupdated
              , left([Label], charindex(' ', Label)-1)
              , substring([Label], charindex(' ', [Label])+1, 10)
              , [Type]
       from   yourTable
)
select coalesce(yt1.[index], yt2.[index]) [index]
       , max(coalesce(yt1.dateupdated, yt2.dateupdated)) dateupdated
       , coalesce(yt1.label, yt2.Label) as Label
       , max(yt1.value) as PValue
       , max(yt2.value) as NValue
from cte yt1
full outer join cte yt2
       on yt2.[index] = yt1.[index]
       and yt2.[label] = yt1.[label]
       and yt2.[type] = 'N'
       and yt1.[type] = 'P'
where  (yt2.[type] = 'N' or yt2.[index] is null)
and    (yt1.[type] = 'P' or yt1.[index] is null)

group by coalesce(yt1.[index], yt2.[index]) 
       , coalesce(yt1.label, yt2.Label)

order by 1,2, 3 desc

OUTPUT:

index       dateupdated Label PValue NValue
----------- ----------- ----- ------ ------
124512      2013-05-10  PA    1      2
124513      2013-05-11  NJ    NULL   10
124514      2013-05-12  NY    5      NULL
124515      2013-05-15  MD    20     21
124515      2013-05-15  CT    NULL   22