CODEWITHSUNDEEP
mysqlsqlms-access
Ali
Ali

Reputation: 466

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.

Upvotes: 2

Views: 221

Answers (6)

Paddy Carroll
Paddy Carroll

Reputation: 538

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

Upvotes: 1

user330315
user330315

Reputation: 

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.

Upvotes: 5

Andriy M
Andriy M

Reputation: 77747

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

Upvotes: 2

Marco
Marco

Reputation: 57603

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

Upvotes: 3

andr
andr

Reputation: 1646

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

Upvotes: 1

Adam Dymitruk
Adam Dymitruk

Reputation: 129782

Use a pivot table:

http://www.ehow.com/i/#article_5336679

Hope this helps

Upvotes: 2

Related Questions