Hello all, The below tutorial will help you in displaying SQL row data as a column wise with user specific column name.

here, I've a table with #Temp1 name that contains the following columns Id, ReferenceId and Total.

SQL Code:--

CREATE TABLE #Temp1 
( Id int, ReferenceId  int, Total int );

INSERT INTO #Temp1 VALUES (1,11,81);
INSERT INTO #Temp1 VALUES (2,11,84);
INSERT INTO #Temp1 VALUES (3,11,85);
INSERT INTO #Temp1 VALUES (4,11,87);
INSERT INTO #Temp1 VALUES (5,11,105);
INSERT INTO #Temp1 VALUES (6,11,104);

INSERT INTO #Temp1 VALUES (7,12,215);
INSERT INTO #Temp1 VALUES (8,12,198);
INSERT INTO #Temp1 VALUES (9,12,104);
INSERT INTO #Temp1 VALUES (10,12,123);
INSERT INTO #Temp1 VALUES (11,12,147);
INSERT INTO #Temp1 VALUES (12,12,231);

INSERT INTO #Temp1 VALUES (13,14,78);
INSERT INTO #Temp1 VALUES (14,14,96);
INSERT INTO #Temp1 VALUES (15,14,97);
INSERT INTO #Temp1 VALUES (16,14,99);
INSERT INTO #Temp1 VALUES (17,14,147);
INSERT INTO #Temp1 VALUES (18,14,302);

Now, I need to display output like the image below. Im assuming there are maximum 6 records for a particular ReferenceId and I want to show Total values under column header like Score1, Score2,......Score6 for every distinct ReferenceId.

Somehow I manage to achieve the same using this...

 SELECT ReferenceId, IsNull([1], 0) as 'Score1', IsNull([2], 0) as 'Score2'
        , IsNull([3], 0) as 'Score3', IsNull([4], 0) as 'Score4'
        , IsNull([5], 0) as 'Score5', IsNull([6], 0) as 'Score6'     
       FROM(
        SELECT ReferenceId,r_id,Total FROM (
            SELECT * ,ROW_NUMBER() OVER (PARTITION BY ReferenceId ORDER BY ReferenceId) as r_id
            FROM #Temp1) AS a WHERE r_id<=6
        )AS P
    PIVOT(
        MAX(Total) FOR r_id in ([1],[2],[3],[4],[5],[6])
    )AS Pvt