Hi,

I didn't notice that the resulting columns need to be dynamic. Sorry.

As far as I know, dynamic pivots only can be done using dynamic SQL.

In order to sort the result values and to built and reference the column names I used a CTE to add a row number per caseid and code.

-- step 1: create separate table that will hold the row_number per code

CREATE TABLE #test (caseid INT, row INT, code CHAR(3))

-- step 2: fill the temp table with test data and row_number

;WITH CTE_test(caseid, row, code)

AS

(

SELECT caseid,

row_number() OVER(PARTITION BY caseid ORDER BY caseid, code),

code

FROM test

)

INSERT INTO #test

SELECT caseid, row, code

FROM CTE_test

-- step 3: build dynamic SQL

DECLARE @sql nvarchar(max)

SET @sql = N'SELECT caseid'

SELECT @sql = @sql + ',MAX(CASE WHEN row='+CAST(a.row AS CHAR(5))+' THEN code ELSE '''' END) AS [Col'+CAST(a.row AS CHAR(5))+']'

FROM #Test a GROUP BY a.row

ORDER BY a.row

SET @sql = @sql + N'

FROM #Test

GROUP BY caseid

ORDER BY caseid'

PRINT @sql -- For Debugging

--EXEC sp_executesql @sql

/* results

caseidCol1Col2Col3Col4Col5

1AAABBBCCCDDD

2CCC

3AAABBBCCCDDDEEE

*/