Columns to Raws possible?

[Pietro]

Hi I've a query that looks like this:

Language Activity1Volume Activity2Volume Activity3Volume Activity4Volume
English 3 0 5
9
French 9 4 15
9
Spanish 3 5 20
3

How to make it look like this:
Activity Volume
Activity1Volume-English 3
Activity1Volume-Spanish 3
Activity1Volume-French 9
Activity2Volume-English 0
Activity2Volume-Spanish 5
Activity2Volume-French 4
....... etc.

 

[KARL DEWEY]

Use a union query to align your data ---
SELECT Language Activity1Volume AS Volume
FROM YourTable
UNION ALL SELECT Language Activity2Volume AS Volume
FROM YourTable
UNION ALL SELECT Language Activity3Volume AS Volume
FROM YourTable
UNION ALL SELECT Language Activity4Volume AS Volume
FROM YourTable;

 

[Ken Sheridan]

You can return a result set using a UNION ALL operation along the
lines Karl describes, but the problem arises from the fact that the
underlying table is poorly designed. By having separate columns for
each activity you are doing what's known as 'encoding data as column
headings'. A fundamental principle of the database relational model,
'the information principle' is that data is stored as explicit values
at column positions in rows in tables, and in no other way.

What you can do is create a new table with columns Language, Activity
and Volume and then execute four 'append' queries along the following
lines, stating with:

INSERT INTO YourNewTable (Language, Activity, Volume)
SELECT Language, "Actvity1", Activity1Volume
FROM YourOldTable
WHERE Activity1Volume IS NOT NULL;

and then:

INSERT INTO YourNewTable (Language, Activity, Volume)
SELECT Language, "Actvity2", Activity2Volume
FROM YourOldTable
WHERE Activity2Volume IS NOT NULL;

and so on. Once you are happy that your new table is correctly
populated you can delete the old one and rename the new one.

Ken Sheridan
Stafford, England