?
Solved

T-SQL

Posted on 2011-10-07
6
Medium Priority
?
307 Views
Last Modified: 2012-05-12
Hi I have the following test_tb as example and looking to return the result as:

Test_TB

STD_ID      STD_NAME      grade_1      grade_2      grade_3
------      --------------      -------      -------      -------
1234      John Doe        80      60      90
2345      Mary Doe       95      88      80
3456      John Smith      80      70      60
4567      Mary Smith      60      65      80


result:

std_id      grade
------      -----
1234      80
1234      60
1234      90
0
Comment
Question by:jfreeman2010
  • 3
  • 2
6 Comments
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1332 total points
ID: 36933720
this can only be done by UNION query, aka:

select STD_ID, Grade_1 as grade FROM yourtable
UNION ALL
select STD_ID, Grade_2 as grade FROM yourtable
UNION ALL
select STD_ID, Grade_3 as grade FROM yourtable
0
 
LVL 25

Assisted Solution

by:TempDBA
TempDBA earned 668 total points
ID: 36933726
Select STD_ID,grade_1 as grade
from <tableName>
union all
Select STD_ID,grade_2 as grade
from <tableName>
union all
Select STD_ID,grade_3 as grade
from <tableName>
order by STD_ID
0
 

Author Comment

by:jfreeman2010
ID: 36933974
Thanks for the response, I will try it out, just want to know can I use UNPIVOT?  I am not sure how to use it either. thanks.
0
[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

 

Author Comment

by:jfreeman2010
ID: 36934074
It posible also to have the heading column in the row like:

std_id      grade grade_name
------      -----     ------------
1234      80        grade_1
1234      60        grade_2
1234      90        grade_3

0
 
LVL 143

Assisted Solution

by:Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3] earned 1332 total points
ID: 36934093
sure:
select STD_ID, Grade_1 as grade, 'grade_1' grade_name FROM yourtable
UNION ALL
select STD_ID, Grade_2 as grade, 'grade_2' grade_name  FROM yourtable
UNION ALL
select STD_ID, Grade_3 as grade, 'grade_3' grade_name  FROM yourtable

Open in new window

0
 

Author Comment

by:jfreeman2010
ID: 36934107
GOT IT.  THANK YOU!!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is basically a blog post I wrote recently. I've found that SARGability is poorly understood, and since many people don't read blogs, I figured I'd post it here as an article. SARGable is an adjective in SQL that means that an item can be fou…
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

616 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question