i have to create a matrix this procedure will return me a same row name with different column name how should i create a matrix that give 4 column against corresponding row

See more:

SQL-Server-2005

SQL-Server

, +

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