Join the social network of Tech Nerds, increase skill rank, get work, manage projects...
 
  • How to display SQL row data as a column wise with user specific column name

    • 0
    • 1
    • 1
    • 1
    • 0
    • 0
    • 2
    • 0
    • 3.17k
    Comment on it

    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

    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

    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
    
    How to display SQL row data as a column wise with user specific column name

 0 Comment(s)

Sign In
                           OR                           
                           OR                           
Register

Sign up using

                           OR                           
Forgot Password
Fill out the form below and instructions to reset your password will be emailed to you:
Reset Password
Fill out the form below and reset your password: