SQL Server reporting services

Question

how to show all values of a particular field in a text box ??? ie. for eg. when u run the SP, u'll be getting 3 rows. and i want to show the (eg.empname) in a textbox each value separated by a comma. (ram, john, sita).

Answer 1

I had this problem the other day. If you are using SQL 2005 you can use the CROSS APPLY function.

Here is a sample;

Structure; 
ID TYPE TEXT
1 1 Ram
2 1 Jon
3 2 Sita
4 2 Joe


Expecteed Output;
ID TYPE TEXT
1 1 Ram, Jon
2 2 Sita, Joe

Query; 
SELECT t.TYPE,LEFT(tl.txtlist,LEN(tl.txtlist)-1)
FROM(SELECT DISTINCT TYPE FROM Table)t
CROSS APPLY (SELECT TEXT + ','
             FROM Table
             WHERE TYPE=t.TYPE
             FOR XML PATH(''))tl(txtlist)

Hope this helps :)

Remember you'll need to select this as something in your sp, then bind that to the textbox on your report. Good luck!

Answer 2

In 2005 the cross apply looks like a good solution (haven't used it myself). I have usually solved this by creating a UDF that concatenates the values by looping through a cursor.

Answer 3

The question should be "How to concatenate rows" http://databases.aspfaq.com/general/how-do-i-concatenate-strings-from-a-column-into-a-single-row.html

CROSS APPLY works or FOR XML are good alternatives vs a cursor

SELECT 
CustomerID, 
SalesOrderIDs = REPLACE( 
    ( 
        SELECT 
            SalesOrderID AS [data()] 
        FROM 
            Sales.SalesOrderHeader soh 
        WHERE 
            soh.CustomerID = c.CustomerID 
        ORDER BY 
            SalesOrderID 
        FOR XML PATH ('') 
    ), ' ', ',') 

FROM Sales.Customer c ORDER BY CustomerID

You could use a Number/Tally table as well