Pivot data based on particular ID without hardcoding

I have a table called ParamDetails with the following data

Aslvl_ID    Value   Score      Comments
1        123.0000   12.3000     Usha P-6914
1        321.0000   64.2000     Usha P-6914
1        456.0000   136.8000    Usha P-6914
1        654.0000   261.6000    Usha P-6914

6        147.0000   14.7000     Bhaskar L-17957
6        741.0000   148.2000    Bhaskar L-17957
6        258.0000   77.4000     Bhaskar L-17957
6        852.0000   340.8000    Bhaskar L-17957

11       4569.0000  456.9000    Murali Prasad K-21632
11       9654.0000  1930.8000   Murali Prasad K-21632
11       4789.0000  1436.7000   Murali Prasad K-21632
11       9874.0000  3949.6000   Murali Prasad K-21632

the requirement is to write a query to display data in below format.i have tried pivot and other few things but did not find any success. please help me with query or suggestion to get the expected output.

expected output:

1 Value    1 score     1 Comments    6 Value    6 score     6 Comments        11 Value    11 score     11 Comments 
123.0000    12.3000     Usha P-6914  147.0000   14.7000     Bhaskar L-17957   4569.0000  456.9000    Murali Prasad K-21632
321.0000    64.2000     Usha P-6914  741.0000   148.2000    Bhaskar L-17957   9654.0000  1930.8000   Murali Prasad K-21632
456.0000    136.8000    Usha P-6914  258.0000   77.4000     Bhaskar L-17957   4789.0000  1436.7000   Murali Prasad K-21632
654.0000    261.6000    Usha P-6914  852.0000   340.8000    Bhaskar L-17957   9874.0000  3949.6000   Murali Prasad K-21632

Solved

You can use a dynamic query to make this.

DECLARE @ColumnNames NVARCHAR(MAX) =''
SELECT  @ColumnNames = @ColumnNames + ', ' + QUOTENAME ( ColName + ' Value' ) + ', ' + QUOTENAME ( ColName +' Score' )  + ', ' + QUOTENAME ( ColName +' Comments' )  
FROM (SELECT DISTINCT CONVERT(VARCHAR,Aslvl_ID) ColName, Aslvl_ID FROM ParamDetails) AS T ORDER BY Aslvl_ID 
SET @ColumnNames = STUFF(@ColumnNames,1,1,'')



DECLARE @SqlText NVARCHAR(MAX)
SET @SqlText  = 'SELECT * FROM 
                    (SELECT ROW_NUMBER() OVER(PARTITION BY Aslvl_ID ORDER BY Aslvl_ID) RN,  CONVERT(VARCHAR,Aslvl_ID) +'' Value'' Col, CONVERT(VARCHAR,Value) Val FROM ParamDetails 
                       UNION ALL
                    SELECT  ROW_NUMBER() OVER(PARTITION BY Aslvl_ID ORDER BY Aslvl_ID) RN, CONVERT(VARCHAR,Aslvl_ID) +'' Score'' Col, CONVERT(VARCHAR,Score) Val FROM ParamDetails 
                       UNION ALL
                    SELECT  ROW_NUMBER() OVER(PARTITION BY Aslvl_ID ORDER BY Aslvl_ID ) RN, CONVERT(VARCHAR,Aslvl_ID) +'' Comments'' Col, Comments Val FROM ParamDetails 
                    ) SRC
                    PIVOT (MAX(Val) FOR Col IN (' + @ColumnNames + ') ) PVT'

EXEC sp_executesql @SqlText

Result:

RN  1 Value          1 Score          1 Comments       6 Value          6 Score          6 Comments       11 Value         11 Score         11 Comments
--- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- ---------------- -----------------------
1   123.0000         12.3000          Usha P-6914      147.0000         14.7000          Bhaskar L-17957  4569.0000        456.9000         Murali Prasad K-21632
2   321.0000         64.2000          Usha P-6914      741.0000         148.2000         Bhaskar L-17957  9654.0000        1930.8000        Murali Prasad K-21632
3   456.0000         136.8000         Usha P-6914      258.0000         77.4000          Bhaskar L-17957  4789.0000        1436.7000        Murali Prasad K-21632
4   654.0000         261.6000         Usha P-6914      852.0000         340.8000         Bhaskar L-17957  9874.0000        3949.6000        Murali Prasad K-21632