Converting Column Headers to Data with Ordered Panel Data (1 Viewer)

[Kooshster]

I've got a rather big pickle of a problem that's left me stumped. I have a giant database of some 40,000+ rows, each detailing a company. Columns in this database include employment by year (columned Emp90, Emp91, etc.), as well as sales (Sales90, Sales91, etc.). The data currently looks like this:

Company Emp90 Emp91 Emp 92...Sales90 Sales91 Sales92...Sales07
1 5 7 9 200 205 209 300
2 6 8 10 200 205 209 300
3 7 9 11 200 205 209 300
4 8 10 12 200 205 209 300
5 9 11 13 200 205 209 300

What I want it to look like is something like this:

Company Year Emp Sales
1 1990 5 200
1 1991 7 205
1 1992 9 209
1 1993 11 214
1 1994 13 230
...
2 1990 6 200

Etc.

Is there any set of SQL code or some query-procedure I could go through to reorder the data? Because the dataset is so large, I can't simply convert it to Excel.

Thanks!!!

 

[pbaldy]

The type of thing in post 4 (I goofed post 2):

http://www.access-programmers.co.uk/forums/showthread.php?t=180615

 

[Kooshster]

Thanks for the link. I tried the query code and wound up with the following error message: "Duplicate Output Alias EMP" (I'm using EMP as short for Employment).

 

[pbaldy]

What does the query look like? I'd get it going with the first couple of fields first. From the sound of it, you've got something like:

SELECT Something as EMP, SomethingElse AS EMP

That would cause an error because you've given 2 fields the same name.

 

[Kooshster]

SELECT DunsNumber, "Emp90_01" as Emp, Emp_01 AS EMPNEW
FROM [Filtered Manufacturing Data]
UNION ALL
SELECT DunsNumber, "Emp91_01" as Emp, Emp_01 AS EMPNEW
FROM [Filtered Manufacturing Data]

I've just reproduced the first two lines. I've fixed the problem I had earlier - now it's asking for a Parameter Value, which just populates each and every field the exact same way.

Thanks for taking the time to help me out!

 

[pbaldy]

If it's prompting for a parameter value, it doesn't recognize the field name. Double check the spelling. Can you post the source table with just a few lines of data for testing, or is it sensitive data?

 

[Kooshster]

I've attached a sample as a .jpg. Here's my new code:

SELECT DunsNumber, "Emp90" as Emp, Emp90 AS EMPNEW
FROM [Filtered Manufacturing Data]
UNION ALL
SELECT DunsNumber, "Emp91" as Emp, Emp91 AS EMPNEW
FROM [Filtered Manufacturing Data]

This seems to be the right track - the Emp columns are now stacked, but organized with all the Emp90 data on top and Emp91 beneath, instead of sorted by DunsNumber.

 

[pbaldy]

Try this:

SELECT DunsNumber, "Emp90" as Emp, Emp90 AS EMPNEW
FROM [Filtered Manufacturing Data]
UNION ALL
SELECT DunsNumber, "Emp91" as Emp, Emp91 AS EMPNEW
FROM [Filtered Manufacturing Data]
ORDER BY DunsNumber

You can have an ORDER BY clause, but only as part of the last SELECT statement.

 

[Kooshster]

It's backwards (91's above 90) but that was easy to fix. Looks like I shouldn't have much trouble expanding the set to the remainder of the data. Thanks so much!!!!

 

[pbaldy]

No problem, and welcome to the site by the way. Post back if you get stuck.

 

[Kooshster]

One more problem: I can't seem to get the other columns to line up one column over - instead of:

DunsNumber, Year, Emp, Sales

I'm getting:

DunsNumber, Year, Emp
Sales

My code for those lines:

SELECT DunsNumber, "1990" as Year, Emp90 AS EMPNEW
FROM [Filtered Manufacturing Data]
UNION ALL
SELECT DunsNumber, "1990" as Year, Sales90 AS SALESNEW
FROM [Filtered Manufacturing Data]

Do I need to refashion the ["1990" as year] as something else?

 

[pbaldy]

I would think you'd want one SELECT for each year:

SELECT DunsNumber, "1990" as Year, Emp90 AS EMPNEW, Sales90 AS SALESNEW
FROM [Filtered Manufacturing Data]
UNION ALL
SELECT DunsNumber, "1991" as Year, Emp91 AS EMPNEW, Sales91 AS SALESNEW
FROM [Filtered Manufacturing Data]

 

[Kooshster]

Works perfectly! Thanks again.