CODEWITHSUNDEEP
sqlsql-server-2008
user1957907
user1957907

Reputation: 21

SSRS Multiple Lines of data

My SQL Server 2008 report returns me multiple lines in a table. A simple select statement returns two columns, one is the record number the other is a sector. The sector column can contain any one of 6 different values.

EDIT: NULL values are allowed in the sector column.

I want this data to be in one line in my table.

Lets say record number 1 has Sector A, Sector C and Sector E and Record 2 has Sector B and Sector C And Record 3 has none.

I am after three lines of data. I also need it displayed two ways. One so that all sectors appear in the same cell separated by commas. The other is a separate Cell for each category

Record number  | Sector
1              | A, C, E
2              | B, C
3              |

Or

Record Number | Sector A | Sector B | Sector C | Sector D | Sector E
1             |  A       |          | C        |          | E
2             |          | B        | C        |          | 
3             |          |          |          |          |

At the moment my report gives me 6 rows.

Record Number | Sector
1             | A
1             | C
1             | E
2             | B
2             | C
3             |

Is there a way of working around this problem?

I am using the query designer rather than writing the SQL statements.

Upvotes: 1

Views: 839

Answers (1)

Taryn
Taryn

Reputation: 247730

In order to get the first result that you want with the comma-separated list of sector values, you will want to use FOR XML PATH and STUFF. The code will be:

select t1.recordnumber,
  STUFF((SELECT ', ' + t2.sector
         from yourtable t2
         where t1.recordnumber = t2.recordnumber
          FOR XML PATH (''))
          , 1, 1, '')  AS Sector
from yourtable t1
group by t1.recordnumber

See SQL Fiddle with Demo. The result is:

| RECORDNUMBER |   SECTOR |
---------------------------
|            1 |  A, C, E |
|            2 |     B, C |
|            3 |   (null) |

Then to get the result of the recordNumber in a single row, you can use the PIVOT function:

select *
from
(
  select recordNumber, sector
  from yourtable
) src
pivot
(
  max(sector)
  for sector in (A, B, C, D, E)
) piv;

See SQL Fiddle with Demo. The result of this query is:

| RECORDNUMBER |      A |      B |      C |      D |      E |
-------------------------------------------------------------
|            1 |      A | (null) |      C | (null) |      E |
|            2 | (null) |      B |      C | (null) | (null) |
|            3 | (null) | (null) | (null) | (null) | (null) |

Upvotes: 2

Related Questions