all data in one row - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

all data in one row

Author

Topic

bekeer020
Starting Member

24 Posts

Posted - 2011-10-17 : 16:22:08

hello all

I have problem when i try to do some thing i will explain my problem now

i have this tables

Student:
-----------
St_ID
St_Name

Class_Register:
------------
St_ID
Course_Name
Mark1
Mark2
Mark3
Mark4
Mark5
Mark6

and my data in Class_Register table like this for example
1-English-1-2-3-4-5-5
1-Math-10-20-10-20-10-10
2-Computer-10-1-10-20-20-5
2-Skills-10-10-10-20-20-5

now i want procedure to show all the data for student in one row (Course_name & Total Marks) like this:

St_ID Course_name1 Total1 Course_name2 Total2 Course_name3 Total3
1-English-20-Math-80
2-Computer-66-Skills-75

Note : Maximam courses in one row 6

I want to show course name with his total marks for each student in one row only

Can any one help me

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 00:22:48

[code]
SELECT St_ID,
MAX(CASE WHEN Rnk=1 THEN CourseName ELSE NULL END) AS Course_name1,
SUM(CASE WHEN Rnk=1 THEN Mark ELSE 0 END) AS Total1,
MAX(CASE WHEN Rnk=2 THEN CourseName ELSE NULL END) AS Course_name2,
SUM(CASE WHEN Rnk=2 THEN Mark ELSE 0 END) AS Total2,
...
MAX(CASE WHEN Rnk=6 THEN CourseName ELSE NULL END) AS Course_name6,
SUM(CASE WHEN Rnk=6 THEN Mark ELSE 0 END) AS Total6
FROM
(
SELECT St_ID,Course_Name,Mark,DENSE_RANK() OVER(PARTITION BY St_ID ORDER BY Course_Name) AS Rnk
FROM Class_Register c
UNPIVOT(MarkValue FOR MarkHeader IN([Mark1],[Mark2],[Mark3],[Mark4],[Mark5],[Mark6]))u
)m
GROUP BY St_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bekeer020
Starting Member

24 Posts

Posted - 2011-10-18 : 01:21:52

SELECT St_ID,Course_Name,Mark,

can you tell me about Mark column he tell me invalid column name

also
MarkValue FOR MarkHeader
is this fixed names?

thank you

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 01:57:12

ha...sorry tht was a typo


SELECT St_ID,
MAX(CASE WHEN Rnk=1 THEN CourseName ELSE NULL END) AS Course_name1,
SUM(CASE WHEN Rnk=1 THEN MarkValue ELSE 0 END) AS Total1,
MAX(CASE WHEN Rnk=2 THEN CourseName ELSE NULL END) AS Course_name2,
SUM(CASE WHEN Rnk=2 THEN MarkValue ELSE 0 END) AS Total2,
...
MAX(CASE WHEN Rnk=6 THEN CourseName ELSE NULL END) AS Course_name6,
SUM(CASE WHEN Rnk=6 THEN MarkValue ELSE 0 END) AS Total6
FROM
(
SELECT St_ID,Course_Name,MarkValue,DENSE_RANK() OVER(PARTITION BY St_ID ORDER BY Course_Name) AS Rnk
FROM Class_Register c
UNPIVOT(MarkValue FOR MarkHeader IN([Mark1],[Mark2],[Mark3],[Mark4],[Mark5],[Mark6]))u
)m
GROUP BY St_ID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bekeer020
Starting Member

24 Posts

Posted - 2011-10-18 : 02:22:28

Dear Visakh16

Note: TOT column already has the total of Mark1-----Mark6
this return good result but return result in seprate row like this

1-Math-null-English-null-Arabic-null-Geog-60
1-Math-null-English-null-Arabic-120-Geog-null
1-Math-80-English-null-Arabic-null-Geog-null

I want it in one row only all tha data about student

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-18 : 02:28:12

see my example you should have only Client_ID in the group by. other fields if you want to display you need to join to this table seperately outside the select

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-03 : 14:03:34

[code]

SELECT St_ID,
MAX(CASE WHEN Rnk=1 THEN CourseName ELSE NULL END) AS Course_name1,
SUM(CASE WHEN Rnk=1 THEN MarkValue ELSE 0 END) AS Total1,
MAX(CASE WHEN Rnk=2 THEN CourseName ELSE NULL END) AS Course_name2,
SUM(CASE WHEN Rnk=2 THEN MarkValue ELSE 0 END) AS Total2,
...
MAX(CASE WHEN Rnk=6 THEN CourseName ELSE NULL END) AS Course_name6,
SUM(CASE WHEN Rnk=6 THEN MarkValue ELSE 0 END) AS Total6
FROM
(
SELECT St_ID,Course_Name,SUM(MarkValue) AS MarkValue,DENSE_RANK() OVER(PARTITION BY St_ID ORDER BY Course_Name) AS Rnk
FROM Class_Register c
UNPIVOT(MarkValue FOR MarkHeader IN([Mark1],[Mark2],[Mark3],[Mark4],[Mark5],[Mark6]))u
GROUP BY St_ID,Course_Name
)m
GROUP BY St_ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources