How can I convert this table:
+---------------------+-------------------------------+-------+-------+ | PropertyName | Emergency | Count | Total | +---------------------+-------------------------------+-------+-------+ | IH | No | 8 | 12 | | IH | No Water | 1 | 12 | | IH | Smoke Alarm not working | 1 | 12 | | IH | Broken Lock - Exterior | 1 | 12 | | IH | Leaking Water | 1 | 12 | | GG | No | 5 | 10 | | GG | Leaking Water | 3 | 10 | | GG | Property Damage (Significant) | 1 | 10 | | GG | Toilet - Clogged (1 Bathroom) | 1 | 10 | | PLB | No | 5 | 10 | | PLB | Resident Locked Out | 2 | 10 | | PLB | Smoke Alarm not working | 1 | 10 | | PLB | Tub - Clogged (1 Bathroom) | 1 | 10 | | PLB | Leaking Water | 1 | 10 | +---------------------+-------------------------------+-------+-------+
to something like this:
+---------------------+-------------------------------+-------+-------+ | PropertyName | Emergency | Count | Total | +---------------------+-------------------------------+-------+-------+ | IH | No | 8 | 12 | | | No Water | 1 | | | | Smoke Alarm not working | 1 | | | | Broken Lock - Exterior | 1 | | | | Leaking Water | 1 | | | GG | No | 5 | 10 | | | Leaking Water | 3 | | | | Property Damage (Significant) | 1 | | | | Toilet - Clogged (1 Bathroom) | 1 | | | PLB | No | 5 | 10 | | | Resident Locked Out | 2 | | | | Smoke Alarm not working | 1 | | | | Tub - Clogged (1 Bathroom) | 1 | | | | Leaking Water | 1 | | +---------------------+-------------------------------+-------+-------+
What I want to do is if the PropertyName is the same as the previous row succeeding row should be omitted. This should be the same for the Total.
I can’t try LAG/LEAD because we are only using SQL SERVER 2008 – i know it’s a bummer.
Advertisement
Answer
You can generate row numbers in a CTE expression, then select top most row numbers per PropertyName:
;with tableWithRowNums as(
select ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum,
PropertyName,
Emergency,
Count,
Total
from the_table ),
first_rows as (
select min(rownum) as first_row,
PropertyName
from tableWithRowNums
group by rowNums.PropertyName
)
select ISNULL(first_rows.PropertyName, ''),
tableWithRowNums.Emergency,
tableWithRowNums.Count,
tableWithRowNums.Total
from tableWithRowNums
left join first_rows on first_rows.first_row = tableWithRowNums.rownum
order by tableWithRowNums.rownum;