MSSQL UNPIVOT and PIVOT example

MSSQL UNPIVOT and PIVOT example

drop table #tmp_1;

SELECT UB92_Exp1Id, row_number() over (order by DxCode) as DxIdx, DxCode, DxCodes
into	#tmp_1
FROM   
   (SELECT	UB92_Exp1Id
		,UB_67 as DiagA
		,UB_68 as DiagB
		,UB_69 as DiagC
		,UB_70 as DiagD
		,UB_71 as DiagE
		,UB_72 as DiagF
		,UB_73 as DiagG
		,UB_74 as DiagH
		,UB_75 as DiagI
		,UB_67_I as DiagJ
		,UB_67_J as DiagK
		,UB_67_K as DiagL
	FROM Rey.UB92_Exp1
	where FileNo='xxxxxx') p  
UNPIVOT  
   (DxCodes FOR DxCode IN   
      (DiagA, DiagB, DiagC, DiagD, DiagE, DiagF, DiagG, DiagH, DiagI, DiagJ, DiagK, DiagL)  

) AS unpvt;  
  

select * from #tmp_1

SELECT	*
FROM
(
	select	a.UB92_Exp1Id, 'Diag'+Char(64+a.DxIdx) as Dx, a.DxCodes
	from	#tmp_1 a

) AS src 
PIVOT(
	max(DxCodes) FOR [Dx] IN ([DiagA],[DiagB],[DiagC],[DiagD],[DiagE],[DiagF],[DiagG],[DiagH],[DiagI],[DiagJ],[DiagK],[DiagL])
) AS DiagRow;