-1

I have a table that has ID, AddrID, and Addr columns.

The ID can be attached to multiple Addr values in which each address has it own ID.

I am trying to make it so that there is a new line to each ID when it has multiple Addresses loaded And not repeat the ID. So in essence not each row for every record.

Hope it makes sense.

This will eventually become an SSRS report.

The desired output would be something as so:

+----+--------+------------+
| ID | AddrID |    Addr    |
+----+--------+------------+
|  1 | S1     | 123 N St   |
|  2 | S2     | 456 S ST   |
|    | S3     | 789 W ST   |
|    | S4     | 987 E ST   |
|  3 | S1     | 123 N St   |
|    | S5     | 147 Elm ST |
|    | S6     | 258 SQL St |
+----+--------+------------+

I tried to use:

declare @nl as char(2) = char(13) + char(10)

but its just not working.

cstrife
  • 13
  • 2
  • How would inserting a new line get rid of an ID. I that 3 rows for ID = 2 or 1 row? – paparazzo Aug 20 '15 at 22:06
  • 3
    Do you really need do this in DB, cannot you just do it in presentation layer in SSRS RDL? It is only matter of presenting things check [How can I merge rows](http://stackoverflow.com/questions/11452140/how-can-i-merge-rows-of-a-particular-column-having-the-same-value-in-sql-serv) and [similiar](http://stackoverflow.com/questions/15204303/how-to-display-data-in-every-row-and-not-have-merged-columns-due-to-row-grouping). – Lukasz Szozda Aug 20 '15 at 22:13

1 Answers1

0

Presentation should be done in the presentation layer (Reporting Services in this instance) not in the database or query.

You can do this two ways:

Grouping

Add a Row Group on ID and this will happen automatically.

Expression

You can hide the ID field by putting an expression on the Visibility-Hidden property:

=Fields!ID.Value = Previous(Fields!ID.Value)

This hides the ID field if it is the same as the one on the previous row.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • thanks Chris that is very helpful and useful. Is what i was looking for. I should have searched for the issue on the SSRS presentation side. – cstrife Aug 21 '15 at 14:51