Click here to Skip to main content
13,660,717 members
Rate this:
Please Sign up or sign in to vote.
See more:
This is actual table

StudendID   Studentname   StudentMark

  17           raja          80
  17           raja          90 

I want my result to be like below

StudendID   Studentname   StudentMark_1   StudentMark_2

  17           raja          80                90          

Pls help 

What I have tried:

I want to know how to get my result using sql query , any idea
Posted 20-Sep-16 20:19pm
Updated 21-Sep-16 2:49am
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

The link provided in Solution 1 is a good resource but I suspect you will need a little more help.

You don't appear to have any ID column for this table - the table is not fully normalised as you are duplicating data (Studentname) in each row, so StudendID doesn't count as the ID for the table.

That's not a problem until you want to understand in what order the marks were received. An identity column on your table will allow you to ORDER BY the order in which the rows were added to the table. A datetime column that is automatically updated would do the same, but the identity column is much easier - e.g.
create table students
    id int identity(1,1),
	StudendID int,
	Studentname varchar(30),
	StudentMark int
You will need to know the rank or number of each mark in relation to each student (assuming this table will have more than one student in it) in order to get the correct columns in your results.

To demonstrate what I mean I inserted this data in to the table
insert into students values
(17, 'raja',  105), (17, 'raja', 80), (17, 'raja',  90),
(18, 'malik', 85), (18, 'malik', 95),
(19, 'sanoop',75), (19, 'sanoop',85)
then ran this query
select StudendID, Studentname, StudentMark, 
from students
which gives me these results
StudendID Studentname   StudentMark rn
17	  raja	        105	    1
17	  raja	        80	    2
17	  raja	        90	    3
18	  malik	        85	    1
18	  malik	        95	    2
19	  sanoop	75	    1
19	  sanoop	85	    2

So we can quite clearly see that raja's first mark was 105, his 2nd was 80 and his 3rd was 90.

If we stick that query into the simple pivot example on the link in Solution 1 we get this
select StudendID, Studentname, ISNULL([1],0) StudentMark_1, ISNULL([2],0) StudentMark_2, ISNULL([3],0) StudentMark_3
	select StudendID, Studentname, StudentMark, 
	from students
) as query
	MAX(StudentMark) FOR rn IN ([1],[2],[3])
) as pvt
order by StudendID

with these results
StudendID  Studentname  StudentMark_1 StudentMark_2 StudentMark_3
17	   raja	        105	      80	    90
18	   malik	85	      95	    0
19	   sanoop	75	      85	    0

The only other things worth noting are

- I used MAX(Studentmark) to get the marks into the PIVOT results because a summary function must be used in PIVOT. I could just have easily used SUM or AVG etc because there is only 1 value per student for each value of rn, but if no function is used then an error is thrown.

- I had to surround the 1,2,3 values with square brackets ([1],[2],[3]) in the PIVOT because they represent column names there, not values, and column names cannot start with a numeric.

- I used ISNULL() to tidy up the NULL values in my results for StudentMark_3 for malik and sanoop.
Karthik Bangalore 21-Sep-16 12:19pm
CHill60 21-Sep-16 12:21pm
Thank you
Maciej Los 21-Sep-16 12:40pm
CHill60 22-Sep-16 18:30pm
Thank you!
Rate this: bad
Please Sign up or sign in to vote.

Solution 1


Check this...[^]

Hope this will help you.


This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Cookies | Terms of Service
Web01-2016 | 2.8.180810.1 | Last Updated 21 Sep 2016
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100