Transpose One Column From Multiple Rows in to Multiple Columns in One Row

Question

I am working with healthcare data that comes from a table that only has one column for procedure code billed, but multiple lines of transactions for each claim when multiple procedure codes are billed.

The specific issue I'm struggling with is I want to see up to three (possibly more, depending on how complicated the answer is) procedure codes for each grouped ClaimID, as well as a sum of the Amount on only one row per ClaimID. Is there a way to achieve this? I have attempted to use case statements in my select, as well as PIVOT and ROW_NUMBER functions without any luck.

When I run the following simple query, the results look like this:

    select originalclaimid, procedurecode, sum(amount) as 'AR Outstanding'

    from TABLE

    group by ORIGINALCLAIMID, PROCEDURECODE

ClaimID	ProcedureCode	AROutstanding
1234	99599	20.00
1234	89898	0
1234	77878	10.00
2344	11112	0
2344	12223	5.00
3335	45454	0

The output I desire is:

ClaimID	ProcedureCode1	ProcedureCode2	ProcedureCode3	AROutstanding
1234	99599	89898	77878	30.00
2344	11112	12223	N/A (NULL is OK)	5.00
3335	45454	N/A	N/A	0

Answer 1

Ok, forget that last post. I think this will work for you.

--drop table testing
--drop table #tmpStud

create table testing (
    ClaimID INT
    , ProcedureCode INT
    , AROutstanding INT
)

INSERT INTO testing (ClaimID, ProcedureCode, AROutstanding) VALUES
 (1234,99599,20)
, (1234,89898,0)
, (2344,77878,10)
, (2344,12223,5)
, (3335,45454,0)

select
    'Procedure' + CONVERT(NVARCHAR(150),ROW_NUMBER () OVER (PARTITION BY ClaimID ORDER BY ClaimID)) AS ClaimNo
    , *
into #tmpStud
from testing

select * from #tmpStud


declare @distinct nvarchar(max) = ''


declare @max int = 33
        , @loop int = 1

while (@loop < @max)
    begin
        if(@loop = 1) begin
            set @distinct = @distinct + '[Procedure' + Convert(nvarchar(20),@loop) + ']'
            set @loop = @loop + 1
        end
        else begin
            set @distinct = @distinct + ',[Procedure' + Convert(nvarchar(20),@loop) + ']'
            set @loop = @loop + 1
        end
    end

print(@distinct)

exec ('
select
    *
from (
    select
        *
    FROM #tmpStud
) AS s PIVOT
(
    MAX(ClaimID)
    FOR ClaimNo IN (' + @distinct + ')
) AS pvt
')