SQL query statement
How can I change from
SID Name Math English French
1 Sam 16 17 19
2 Tom 18 14 12
3 Al 90 33 2
to:
SID subject Mark
1 Math 开发者_高级运维 16
1 English 17
1 French 19
2 Math 18
2 English 14
2 French 12
3 Math 90
3 English 33
3 French 2
using SQL (MYsql and MS Access if possible) ?
Thanks.
SELECT sid, 'Math' as subject, math as mark
FROM your_table
UNION ALL
SELECT sid, 'English' as subject, english as mark
FROM your_table
UNION ALL
SELECT sid, 'French' as subject, french as mark
FROM your_table
But the root cause of your problem is a wrong database design. Those subjects shouldn't be columns in the first place and should be stored in a table very much like your desired output.
Edit
So what does it do?
SELECT sid, 'Math' as subject, math as mark
FROM your_table
Returns the sid
column, a "virtual" column with the hardcoded value 'Math'
that is given the name subject
. As you have not stored the value 'Math'
somewhere, this had to be hardcoded. Then at last it also selects the column math
using the name mark
instead. Note the difference between math
and 'Math'
- one is a column the other one a string literal because of the single quotes.
This is done for all three subjects (if you had four subjects, you'd need four parts in the UNION)
The UNION ALL combines all three SELECTs into one single query. andr solution (which has been downvoted by someone who didn't understand it) makes this even clearer by explicitely putting that into a derived table (or inline view).
Run each SELECT on its own to see what the individual parts are doing.
The part as mark
is called a "column alias" and can also be used to retrieve columns with the same name from different tables in a join and still have unique names in the result set.
Try this:
SELECT SID,'Math' subject, Math Mark
FROM table
UNION ALL
SELECT SID,'English' subject, English Mark
FROM table
UNION ALL
SELECT SID,'French' subject, French Mark
FROM table
Use a pivot table:
http://www.ehow.com/i/#article_5336679
Hope this helps
In SQL Server 2005 or higher you can use UNPIVOT:
SELECT
SID,
Subject,
Mark
FROM (
SELECT SID, Math, English, French
FROM atable
) s
UNPIVOT (
Mark FOR Subject IN (Math, English, French)
) u
select * from
( select sid, 'Math' as subject, math as mark from t
union all
select sid, 'English' as subject , English as Mark from t
union all
select sid, 'French' as subject, French as mark from t
) order by 1;
Should do the trick
select * from (
select SID, 'Math' as subject, math as mark from table
union
select SID, 'English' as subject, English as mark from table
union
select SID, 'French' as subject, french as mark from table
) order by sid asc
where table =
SID Name Math English French
1 Sam 16 17 19
2 Tom 18 14 12
3 Al 90 33 2
精彩评论