How can I convert this table:
x
+---------------------+-------------------------------+-------+-------+
| 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;