I've got a table with records as follows
ID - Type - Code - Order
1 - A - 3 - 1
1 - A - 5 - 2
1 - B - 88 -1
1 - B - 99 -2
1 - B - 123 -3
2 - A - 7 - 1
2 - A - 6 - 2
2 - A - 1 - 3
2 - A - 8 - 4
etc..
What I want is to pivot on ID, and produce
ID - A1 - A2 - A3 - A4 - A5 - B1 - B2 - B3 - B4 - B5
1 - 3 - 5 - NULL - NULL - NULL - 88 - 99 - 123 - NULL - NULL
etc..
I can make this work if I restrict it to just one code type, but I can't figure out how to include a second.
I tried prefixing my Order field with an A and B, but this screwed up the ordering.
EDIT:
SELECT [ID],
[1] AS DX1 ,[2] AS DX2 ,[3] AS DX3 ,[4] AS DX4 ,[5] AS DX5 ,[6] AS DX6 ,[7] AS DX7 ,[8] AS DX8 ,[9] AS DX9 ,[10] AS DX10,
[11] AS DX11,[12] AS DX12,[13] AS DX13,[14] AS DX14,[15] AS DX15,[16] AS DX16,[17] AS DX17,[18] AS DX18,[19] AS DX19,[20] AS DX20,
[21] AS DX21,[22] AS DX22,[23] AS DX23,[24] AS DX24,[25] AS DX25,[26] AS DX26,[27] AS DX27,[28] AS DX28,[29] AS DX29,[30] AS DX30,
[31] AS DX31,[32] AS DX32,[33] AS DX33,[34] AS DX34,[35] AS DX35,[36] AS DX36,[37] AS DX37,[38] AS DX38,[39] AS DX39,[40] AS DX40,
[41] AS DX41,[42] AS DX42,[43] AS DX43,[44] AS DX44,[45] AS DX45,[46] AS DX46,[47] AS DX47,[48] AS DX48,[49] AS DX49,[50] AS DX50,
[51] AS DX51,[52] AS DX52,[53] AS DX53,[54] AS DX54,[55] AS DX55,[56] AS DX56,[57] AS DX57,[58] AS DX58,[59] AS DX59,[60] AS DX60,
[61] AS DX61,[62] AS DX62,[63] AS DX63,[64] AS DX64,[65] AS DX65,[66] AS DX66,[67] AS DX67,[68] AS DX68,[69] AS DX69,[70] AS DX70,
[71] AS DX71,[72] AS DX72,[73] AS DX73,[74] AS DX74,[75] AS DX75,[76] AS DX76,[77] AS DX77,[78] AS DX78,[79] AS DX79,[80] AS DX80,
[81] AS DX81,[82] AS DX82,[83] AS DX83,[84] AS DX84,[85] AS DX85,[86] AS DX86,[87] AS DX87,[88] AS DX88,[89] AS DX89,[90] AS DX90,
[91] AS DX91,[92] AS DX92,[93] AS DX93,[94] AS DX94,[95] AS DX95,[96] AS DX96,[97] AS DX97,[98] AS DX98,[99] AS DX99,[100] AS DX100
FROM(
SELECT
FIC.[ID]
,ISNULL(Code.Code, ' ') Code
,FIC.[DWHCodingLineOrder] AS CodingOrder
FROM FIC
INNER JOIN DimCode AS Code WITH(NOLOCK) ON FIC.CodeId = Code.CodeId
WHERE FIC.ArchiveFlag = 0 AND FIC.[CodeType] = 'DIAGN'
) AS SourceTable
PIVOT
(
MAX(DiagCode)
FOR DiagCodingOrder IN (
[1] ,[2] ,[3] ,[4] ,[5] ,[6] ,[7] ,[8] ,[9] ,[10],
[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],
[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],
[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],
[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],
[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],
[81],[82],[83],[84],[85],[86],[87],[88],[89],[90],
[91],[92],[93],[94],[95],[96],[97],[98],[99],[100])
)AS DiagPivot
What I am trying to do is have that, but after DX100, proceed to PROC1 -> PROC100 It is exactly the same code, except CodeType = Proce instead of Diagn
