ALTER PROCEDURE [dbo].[SP_Fetch_RiskManagmentMatrix]
@FormID int=0
as
select objmap.FormID,objmap.ColumnID,objmap.RowID, objCol.Matrix_ColumnName,objRows.Matrix_RowName,objMap.Value
into #temp
from Risk_Management_Mapping as objMap
inner Join Risk_Management_Columns as objCol
on objCol.FormID=objMap.FormID and objCol.Matrix_ColumnID=objMap.ColumnID
inner Join Risk_Management_Rows as objRows
on objRows.FormID=objCol.FormID and objMap.RowID=objRows.Matrix_RowID
where objMap.FormID=@FormID
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX)
select @cols = STUFF((SELECT distinct ',[' + Matrix_ColumnName+']'
from #temp
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
set @query = 'SELECT FormID, ColumnID,RowID, Matrix_RowName as ''RowName'' , ' + @cols + ' from
( select * from #temp as temp
) x
pivot
( max(Value)
for Matrix_ColumnName in (' + @cols + ')
) p '
execute(@query)
drop table #temp
4 1 1 row1 NULL NULL NULL low
4 2 1 row1 colomval NULL NULL NULL
4 2 2 row2 Low NULL NULL NULL
4 2 3 row3 mode NULL NULL NULL
4 2 4 row4 Low NULL NULL NULL
4 2 5 row5 Low NULL NULL NULL
4 3 1 row1 NULL high NULL NULL
4 4 1 row1 NULL NULL updated high NULL