For a TSQL report, I have a one-to-many parent-child relationship.
I want to report both parent and child relationships in the same report, like so, with the parent columns on the left and child columns on the right:
ParentName | ParentNum | ParentIncome | ChildName | ChildNum | ChildAllowance
John | 1 | 50000 | Johnny | 1 | 5
Jane | 2 | 55000 | Jackie | 2 | 10
Jane | 2 | | Billy | 3 | 5
Jane | 2 | | Sally | 4 | 5
Jackie | 3 | 90000 | Monique | 5 | 0
I want to avoid duplicating certain columns, like ParentIncome, because users dump the report output into Microsoft Excel and I want to minimize the chances of them summing columns incorrectly.
Therefore, for each parent, I want to only display ParentIncome once (ParentNum and ParentName OK to repeat.). For rows other than the first row, display a blank cell (i.e., no NULL values).
Currently, I produce this report using a wrapper around a subquery. The wrapper looks like this for all the columns:
CASE WHEN DATA.ROW_NUMBER = 1
THEN DATA.Field1
ELSE '' END AS 'AColumn'
The subquery orders things using OVER and PARTITION BY and ORDER BY, so when ROW_NUMBER = 1, it's always a new parent.
It seems like this is something others have run into, though...in TSQL, can I get the same results in a simpler way, without the extra outer wrapper query to format things?