none
Convert single row in sql

    Question

  • Hi

         any one can tell me the solution for following issue,

    My Sql table is "Student" like

    task_id    qid    eval_id    stud_id    marks
    1               1         2             101        70
    2               3         1             101        90
    3               4         3             101        50
    4               2         4             101        40

    i want query for following output

    stud_id    eval_id_1   eval_id_2   eval_id_3   eval_id_4

    101             90               70               50             40

    Thank in advance,


    Jey www.hoopla.co.in

    Saturday, January 05, 2013 5:17 AM

Answers

  • The better solution to convert rows to columns (PIVOT) is to use CASE WHEN in your scenario as the PIVOT operator in SQL currently suffers from the malady of having to hardcode the PIVOT values. If you want to handle different marks, then use the following
    DECLARE @Student AS TABLE (
    	task_id INT,
    	qid INT,
    	eval_id INT,
    	stud_id INT,
    	marks INT)
    INSERT @Student SELECT 1,1,2,101,70
    INSERT @Student SELECT 2,3,1,101,90
    INSERT @Student SELECT 3,4,3,101,50
    INSERT @Student SELECT 4,2,4,101,40
    
    
    SELECT	Stud_Id
    	   ,SUM(CASE eval_id WHEN 1 THEN marks ELSE NULL END) AS eval_id1
    	   ,SUM(CASE eval_id WHEN 2 THEN marks ELSE NULL END) AS eval_id2
    	   ,SUM(CASE eval_id WHEN 3 THEN marks ELSE NULL END) AS eval_id3
    	   ,SUM(CASE eval_id WHEN 4 THEN marks ELSE NULL END) AS eval_id4
    
      FROM	@Student
    GROUP BY Stud_id

    Saturday, January 05, 2013 3:20 PM

All replies

  • Try using PIVOT - 

    http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/


    Narsimha

    Saturday, January 05, 2013 6:37 AM
  • Hi

    You need to use cross tab query using pivot

    see example :

    Table:
    DaysToManufacture          AverageCost
    
    0                          5.0885
    
    1                          223.88
    
    2                          359.1082
    
    4                          949.4105
    
    SELECT 'AverageCost' AS Cost_Sorted_By_Production_Days, 
    [0], [1], [2], [3], [4]
    FROM
    (SELECT DaysToManufacture, StandardCost 
        FROM Production.Product) AS SourceTable
    PIVOT
    (
    AVG(StandardCost)
    FOR DaysToManufacture IN ([0], [1], [2], [3], [4])
    ) AS PivotTable;
    
    Result:
    Cost_Sorted_By_Production_Days    0         1         2           3       4       
    
    AverageCost                       5.0885    223.88    359.1082    NULL    949.4105
    

    Hope following it you can do ...

    Details on :http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx


    Ahsan Kabir Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread. http://www.aktechforum.blogspot.com/

    Saturday, January 05, 2013 7:04 AM
  • Hi,

    CREATE  TABLE Student(
    task_id INT,
    qid INT,
    eval_id INT,
    stud_id INT,
    marks INT)
    INSERT Student SELECT 1,1,2,101,70
    INSERT Student SELECT 2,3,1,101,90
    INSERT Student SELECT 3,4,3,101,50
    INSERT Student SELECT 4,2,4,101,40
    SELECT 
    stud_id,90 eval_id_1, 70 eval_id_2, 50 eval_id_3, 40 eval_id_4
    FROM
    (SELECT stud_id,marks,task_id  
        FROM Student) T
    PIVOT
    (
    MAX(task_id)
    FOR marks IN ([90], [70],[50], [40])
    ) AS PivotTable;


    Thanks & Regards, sathya

    Saturday, January 05, 2013 2:55 PM
    Moderator
  • The better solution to convert rows to columns (PIVOT) is to use CASE WHEN in your scenario as the PIVOT operator in SQL currently suffers from the malady of having to hardcode the PIVOT values. If you want to handle different marks, then use the following
    DECLARE @Student AS TABLE (
    	task_id INT,
    	qid INT,
    	eval_id INT,
    	stud_id INT,
    	marks INT)
    INSERT @Student SELECT 1,1,2,101,70
    INSERT @Student SELECT 2,3,1,101,90
    INSERT @Student SELECT 3,4,3,101,50
    INSERT @Student SELECT 4,2,4,101,40
    
    
    SELECT	Stud_Id
    	   ,SUM(CASE eval_id WHEN 1 THEN marks ELSE NULL END) AS eval_id1
    	   ,SUM(CASE eval_id WHEN 2 THEN marks ELSE NULL END) AS eval_id2
    	   ,SUM(CASE eval_id WHEN 3 THEN marks ELSE NULL END) AS eval_id3
    	   ,SUM(CASE eval_id WHEN 4 THEN marks ELSE NULL END) AS eval_id4
    
      FROM	@Student
    GROUP BY Stud_id

    Saturday, January 05, 2013 3:20 PM
  • To make a PIVOT data-driven, you have to use dynamic SQL PIVOT:

    http://www.sqlusa.com/bestpractices2005/dynamicpivot/

    Dynamic PIVOT picks up new columns automatically from the tables without programming changes.

    SSRS has built-in dynamic PIVOT.


    Kalman Toth SQL 2008 GRAND SLAM
    New Book: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

    Saturday, January 05, 2013 3:24 PM
    Moderator