How to convert multiple rows into one row in SSRS?

Question

My MSSQL sql query shows the data of Employee Hierarchy with the authcodes as below

ManagerName     EmployeeName    AuthCode
Tim             Peter           csr2
Tim             Peter           sup
Tim             Peter           itil
Tim             Colt            csr2
Tim             Colt            sup

 The SSRS report shows the results listed below, when I chose the report parameter= Tim, the result only shows the top line and not the subsequent AuthCodes whereas the query shows all

EmployeeName    AuthCode
Peter           csr2
Colt            csr2

 I want the data to show like(it should concatenate the authcodes for the employees):

EmployeeName    AuthCode
Peter           csr2, sup, itil
Colt            csr2, sup

Could anyone please suggest what I need to do in SSRS so that the data shows all the authcodes with comma separated values?

Possible duplicate of [Simulating group\_concat MySQL function in SQL Server?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-sql-server) — Tab Alleman, Apr 13 '16 at 19:53
@GeetanjaliSachdeva, check [this](http://stackoverflow.com/questions/27047483/ssrs-distinct-lookupset-function) — alejandro zuleta, Apr 14 '16 at 00:43

score 3 · Answer 1 · answered Apr 13 '16 at 20:54

3

This should work:

=JOIN(LOOKUPSET(Fields!EmployeeName.Value, Fields!EmployeeName.Value, Fields!AuthCode.Value, "DataSet1"), ", ")

The LookupSet get all the AuthCodes based on the EmployeeName.

The JOIN converts the array into a string using a comma delimiter.

answered Apr 13 '16 at 20:54

Hannover Fist

10,393
1
18
39

+Hannover Fist Thanks! That works but i still see some authcodes that are duplicated in a cell for the same employee. How can i get rid off the duplicates? – Geetanjali Sachdeva Apr 13 '16 at 22:11
Unfortunately, neither the **JOIN** nor the **LookupSet** can handle duplicates. Can you make your data DISTINCT? Otherwise you'd need some custom VB code to parse out the duplicates. – Hannover Fist Apr 13 '16 at 23:04

Andrey Kormilitsyn · Answer 2 · 2021-05-30T20:52:16.587

Some addition to Hannover Fist's answer:
If you need distinct your join + lookupset result, you can

Go to your Report's properties -> Code tab

Add some VB code to your report properties

Public Shared Function RemoveDuplicates(m_Array As Object()) As String()

    System.Array.Sort(m_Array)
    Dim k As Integer = 0
    For i As Integer = 0 To m_Array.Length - 1
        If i > 0 AndAlso m_Array(i).Equals(m_Array(i - 1)) Then
            Continue For
        End If
        m_Array(k) = m_Array(i)
        k += 1
    Next

    Dim unique As [String]() = New [String](k - 1) {}
    System.Array.Copy(m_Array, 0, unique, 0, k)
    Return unique

End Function

and edit cell expression:

=JOIN( Code.RemoveDuplicates( LOOKUPSET(Fields!EmployeeName.Value, Fields!EmployeeName.Value, Fields!AuthCode.Value, "DataSet1")), ", ")

How to convert multiple rows into one row in SSRS?

2 Answers2