Flattening several rows into one row

  • Hello

    I have the following situation. I need to put this result in a single line.

    For example:  The result of the should be.

    Claim ID  Cust Name  Entry Date  Reports Printed

    ---------------------------------------------------

    555555    XXXXXX    1/5/2003         1,2,3

    Where the record on one of the table contain A for the first report printed

    and B for second report printed and C for the third.

    The problem is that is it does not group up.

    Example:

    Claim ID  Cust Name  Entry Date  Reports Printed

    ---------------------------------------------------

    555555    XXXXXX    1/5/2003         1

    555555    XXXXXX    1/5/2003         2                                                           555555    XXXXXX    1/5/2003         3

    This is my script on the SQL:

      SELECT Claim.Claim_ID,

                     Cust_Name,

                     Entry_Date,

                    (CASE report_type when 'A' then '1' else '' end) as A,

                    (CASE report_type when 'B' then '2' else '' end) as  B,                        (CASE report_type when 'B' then '3' else '' end) as C,

        FROM Claim,

             report_log

    WHERE (Claim.claim_id *= report_log.claim_id

    GROUP BY Claim_ID

                       Cust_Name,

                       Entry_Date,

    ORDER BY Claim.Claim_ID

    Claim ID  Cust Name  Entry Date  Reports Printed

    ---------------------------------------------------

    555555    XXXXXX    1/5/2003         1

    555555    XXXXXX    1/5/2003         2                                                           555555    XXXXXX    1/5/2003         3

    I need:

    Claim ID  Cust Name  Entry Date  Reports Printed

    ---------------------------------------------------

    555555    XXXXXX    1/5/2003         1,2,3

    By the way, there could be more than just 1,2,3.

    Thanks in Advance

  • You can create a UDF for returning the third column. The UDF would accept claimID  as the parameter and would loop to create a string separated with commans.

    e.g. SELECT Claim.Claim_ID,Cust_Name,Entry_Date,UDF(CLaimID)

       group by Claim.Claim_ID,Cust_Name,Entry_Date

    Hope this helps.....

    You have to dig for tons of dirt to get an ounce of Gold

  • Try adapting this bit of skeleton code ....  should be OK providing your tables are small enough.

    declare @t1 table (claim char(1), cust char(1), report char(1))
    insert into @t1 values ('a','x','1')
    insert into @t1 values ('a','x','2')
    insert into @t1 values ('a','x','3')
    insert into @t1 values ('a','y','1')
    insert into @t1 values ('a','y','2')
    select * from @t1
    declare @t2 table (claim char(1), cust char(1), reports varchar(50))
    declare c1 cursor for select distinct claim, cust from @t1
    declare @claim char(1), @cust char(1), @reports varchar(50)
    open c1
    fetch c1 into @claim, @cust
    while @@fetch_status = 0
    begin
     set @reports = null
     select @reports = coalesce(@reports+',','') + report from @t1 where claim = @claim and cust = @cust order by report
     insert into @t2 values (@claim, @cust, @reports)
     fetch c1 into @claim, @cust
    end
    close c1
    deallocate c1
    select * from @t2
  • creating a UDF for returning several rows into one row

    Somebody posted this nice solution earlier:

    create function ValList ( @anID VARCHAR(5) ) returns varchar(500) as

    BEGIN

    DECLARE @retval VARCHAR(500)

    SET @retval = ''

    SELECT @retVal = @retval + CASE WHEN @retVal = '' THEN '' ELSE ',' END + myVal

    FROM TestMe (NOLOCK) WHERE myID = @anID

    RETURN @retVal

    END

    select DISTINCT myID, dbo.ValList( myID )

    from TestMe

     

  • This solution uses a loop instead of a function

    CREATE TABLE #temp (Claim_ID, Cust_Name, Entry_Date, ReportsPrinted)

    INSERT INTO #temp

        SELECT DISTINCT Claim_ID,Cust_Name,Entry_Date,NULL 

        FROM Claim

    DECLARE @report_type char(1)

    SELECT @report_type = MIN(report_type) 

        FROM report_log

    WHILE (@report_type IS NOT NULL) 

        BEGIN 

        UPDATE t 

            SET ReportsPrinted = COALESCE(ReportsPrinted+','+r.report_type,r.report_type) 

            FROM #temp t 

            INNER JOIN report_log r 

                ON r.Claim_ID = t.Claim_ID 

                AND r.report_type = @report_type 

            SELECT @report_type = MIN(report_type) 

                FROM report_log 

                WHERE report_type > @report_type 

        END

    SELECT * FROM #temp

    DROP TABLE #temp

    Far away is close at hand in the images of elsewhere.
    Anon.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply