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);
![alt text](http://findnerd.s3.amazonaws.com/imagedata/2034/240x160/2034.png)
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.
![alt text](http://findnerd.s3.amazonaws.com/imagedata/2036/240x160/2036.jpg)
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
0 Comment(s)