Click here to Skip to main content
13,146,739 members (89,953 online)
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
 
good
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, 
   ROW_NUMBER() OVER (PARTITION BY StudendID ORDER BY id) AS rn
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
from
(
	select StudendID, Studentname, StudentMark, 
        ROW_NUMBER() OVER (PARTITION BY StudendID ORDER BY id) AS rn
	from students
) as query
PIVOT 
(
	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.
  Permalink  
Comments
Karthik Bangalore 21-Sep-16 12:19pm
   
5
CHill60 21-Sep-16 12:21pm
   
Thank you
Maciej Los 21-Sep-16 12:40pm
   
5ed!
CHill60 22-Sep-16 18:30pm
   
Thank you!
Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Hi,

Check this...

http://web2.codeproject.com/Tips/500811/Simple-Way-To-Use-Pivot-In-SQL-Query[^]

Hope this will help you.


Cheers
  Permalink  

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 |
Web03 | 2.8.170915.1 | Last Updated 21 Sep 2016
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

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