SQL Server Performance

concatination on group by

Discussion in 'SQL Server 2005 Reporting Services' started by sibysjoseph, May 15, 2008.

  1. sibysjoseph New Member

    hi all,The situation is something like this id name ------------------1 lad1 big 2 sen2 zenThe output would be id name ------------------1 lad,big2 sen,zenIs there a way to implement this is Reporting services.This has to be achieved on the fly which the report is being executed !Any help on this is highly appreciated .
  2. MichaelB Member

    I am not sure if I understand. I think you want to know if you can concatinate the results into one long comma delimited string??
  3. MichaelB Member

    cant you do it in the proc then point RS to the proc?
  4. MichaelB Member

    I think this is what you need..create
    table #test(eid int,name varchar(12))insert
    into #testvalues
    (1, 'lad') insert
    into #testvalues
    (1, 'big')insert
    into #testvalues
    (2, 'sen')insert
    into #testvalues
    (2, 'zen')CREATE
    TABLE #curmax(eID int,name varchar(50)) CREATE
    TABLE #result(eID int,name varchar(50)) CREATE TABLE #temp(eID int,name varchar(50)) INSERT
    INTO #result SELECT
    eID,max(name)as name from #testgroup
    by eID INSERT
    INTO #temp SELECT
    * from #result WHILE
    (1=1) BEGININSERT
    INTO #curmax SELECT
    a.eID, max(a.name) FROM #test a join #temp b on a.eID = b.eID where
    a.name < b.name GROUP
    BY a.eID IF
    (@@ROWCOUNT = 0) BREAK UPDATE
    #result SET
    #result.name = b.name+ ',' + a.name FROM
    #result a join #curmax b on a.eID=b.eID truncate
    table #temp INSERT
    INTO #temp SELECT
    * from #curmax truncate
    table #curmax ENDSELECT
    * FROM #result DROP
    table #curmax DROP
    table #temp DROP
    table #result

Share This Page