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

Answer 1

Try using PIVOT - 

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

---

Narsimha

Answer 2

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/

Answer 3

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

Answer 4

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

Answer 5

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