Hello everyone!

I can`t find a solution to the following problem in SQL Server Reporting Services 2005.
I created a table which has multiple columns. It can happen, that some columns have the same values for multiple rows, only the most right column has different values. For example
Column 1 | Column 2 | Column 3
aaa | bbb | 1
aaa | bbb | 2
ccc | ddd | 3
and so on...

But I want it to look like this:
aaa | bbb | 1,2
ccc | ddd | 3
so that in column 3 there are all values that have the same values in column 1 and 2.

How can I do that? Any help is appreciated. Thanks in advance.

Recommended Answers

All 4 Replies

You've this table in your database, RS just run a query to get result displayed in the report, did you develop the query to get this result?

select * from table group by Column1 column2 order by column1

@RamyMahrous: This is the SQL-Statement in RS:

SELECT     PROJEKTE.Name AS Projektname, PROJEKTE.Ort, PROJEKTE.Start_Datum AS Start, PROJEKTE.Ziel_Datum AS Ziel, 
                      PROJEKTE.Tage_Woche AS Umfang, PROJEKTE.Bereich, PROJEKTE.Betreuer_Kurzzeichen AS Betreuer, PERSONEN.Name AS Nachname, 
                      FIRMEN.FA_Name AS Firmenname, PROJEKTE.Beschreibung, PERSONEN.Ist_MA, PROJEKTE.Status
FROM         Projekt_Personen LEFT OUTER JOIN
                      PERSONEN ON Projekt_Personen.Person_ID = PERSONEN.Person_ID LEFT OUTER JOIN
                      PROJEKTE ON Projekt_Personen.Projekt_ID = PROJEKTE.Projekt_ID LEFT OUTER JOIN
                      FIRMEN ON PROJEKTE.FA_ID = FIRMEN.FA_ID
WHERE     (PROJEKTE.Status = 'anfrage akt' OR
                      PROJEKTE.Status = 'projekt akt') AND (PERSONEN.Ist_MA <> 2) OR
                      (PROJEKTE.Status = 'anfrage akt' OR
                      PROJEKTE.Status = 'projekt akt') AND (PERSONEN.Ist_MA = 2) AND (Projekt_Personen.Projekt_Position LIKE 'bar%') AND 
                      (Projekt_Personen.Projekt_Position LIKE '%ma')
GROUP BY PROJEKTE.Betreuer_Kurzzeichen, PROJEKTE.Status, PROJEKTE.Name, FIRMEN.FA_Name, PROJEKTE.Ort, PROJEKTE.Bereich, 
                      PROJEKTE.Beschreibung, PROJEKTE.Start_Datum, PROJEKTE.Ziel_Datum, PROJEKTE.Tage_Woche, PERSONEN.Name, PERSONEN.Ist_MA

The problem would be solved, if I could have the column PERSONEN.Name divided in two columns, based on the WHERE-Statement I`m using.

@almostbob: Sorry, this doesn`t solve the problem, but thanks anyway

Okay, I solved the problem with the SQL-Query (CASE was the key). But that didn`t help solving the problem in Reporting Services. Any more suggestions?

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.