LarryAndro
asked on
SQL Query Turning Row Values into Column
I want to know how to construct a SQL statement to present the data in a different perspective than "by entry." Basically, I want to turn a row into a column. This is hard to explain, but easier to illustrate...
Here are the fields in a SQL table...
id,effectiveYear,stateName ,countyNam e,FIPS,TRH D1
Here's a typical SELECT FROM report...
All Entries Report
id effectiveYear stateName countyName FIPS TRHD1
========================== ========== ========== ========== ====
1531551 2005 Missouri Phelps 01011 25113
1531551 2006 Missouri Phelps 01032 26613
1531551 2007 Missouri Phelps 01051 25145
1531551 2008 Missouri Phelps 01062 28113
1531551 2005 Missouri Johnson 01013 25113
1531551 2006 Missouri Johnson 01035 26613
1531551 2007 Missouri Johnson 01055 25145
1531551 2008 Missouri Johnson 01064 28113
I would like a different orientation on the report. Instead of the effectiveYear being placed on rows, I would like to make the various values into columns. Like this...
FIPS Comparison Report
FIPS FIPS FIPS FIPS
stateName CountyName 2005 2006 2007 2008
========================== ========== ========== =====
Missouri Phelps 01011 01032 01051 01062
Missouri Johnson 01013 01035 01055 01064
In other words, the value of the FIPS for each year is placed in the last 4 columns.
Can someone give me some pointers, or point me toward a tutorial or aid or help, so I know how to do?
Thx, LJA
Here are the fields in a SQL table...
id,effectiveYear,stateName
Here's a typical SELECT FROM report...
All Entries Report
id effectiveYear stateName countyName FIPS TRHD1
==========================
1531551 2005 Missouri Phelps 01011 25113
1531551 2006 Missouri Phelps 01032 26613
1531551 2007 Missouri Phelps 01051 25145
1531551 2008 Missouri Phelps 01062 28113
1531551 2005 Missouri Johnson 01013 25113
1531551 2006 Missouri Johnson 01035 26613
1531551 2007 Missouri Johnson 01055 25145
1531551 2008 Missouri Johnson 01064 28113
I would like a different orientation on the report. Instead of the effectiveYear being placed on rows, I would like to make the various values into columns. Like this...
FIPS Comparison Report
FIPS FIPS FIPS FIPS
stateName CountyName 2005 2006 2007 2008
==========================
Missouri Phelps 01011 01032 01051 01062
Missouri Johnson 01013 01035 01055 01064
In other words, the value of the FIPS for each year is placed in the last 4 columns.
Can someone give me some pointers, or point me toward a tutorial or aid or help, so I know how to do?
Thx, LJA
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Great pointers! Let me peruse and experiment. I'll be back...
awking00,
Is your solution for an Oracle database?
Is your solution for an Oracle database?
ASKER
I am using Cache SQL by www.intersystems.com. Most SQL statements that work on other systems work on Cache SQL. However, I'm used to product difference.
If I see how it's done, and the approach given doesn't work out of the box, I can finesse the process in most instances. Fortunately, the above info definitely gives me a good start on how it's done!
If I see how it's done, and the approach given doesn't work out of the box, I can finesse the process in most instances. Fortunately, the above info definitely gives me a good start on how it's done!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
When I ask a SQL question I throw it into various SQL-related zones knowing the answers might be exact fits. I try to frame my questions generically also. But, you're making good points I think for many other questioners who probably want specific answers for specific SQL products.
ASKER
I'm getting close! Here's my SQL query...
select stateName,countyName,
case effectiveYear when '2001' then trhd1 else 'a' end as Yr2001,
case effectiveYear when '2002' then trhd1 else 'b' end as Yr2002,
case effectiveYear when '2003' then trhd1 else 'c' end as Yr2003,
case effectiveYear when '2004' then trhd1 else 'd' end as Yr2004,
case effectiveYear when '2005' then trhd1 else 'e' end as Yr2005,
case effectiveYear when '2006' then trhd1 else 'f' end as Yr2006,
case effectiveYear when '2007' then trhd1 else 'g' end as Yr2007,
case effectiveYear when '2008' then trhd1 else 'h' end as Yr2008
from std_GMTThresholds
order by statename, countyName
Here's my report...
stateName countyName Yr2001 Yr2002 Yr2003 Yr2004 Yr2005 Yr2006 Yr2007 Yr2008
-------------------------- ---------- ---------- ---------- ---------- ---------- ---------- -----
ALABAMA AUTAUGA COUNTY 28950 b c d e f g h
ALABAMA AUTAUGA COUNTY a 29700 c d e f g h
ALABAMA AUTAUGA COUNTY a b 29700 d e f g h
ALABAMA AUTAUGA COUNTY a b c 29700 e f g h
... not showing the entire report ...
(Copy report to document and display as Courier New and columns will align.)
Notice that my ELSE places letters in the report to make it clear what line is being executed.
I am getting what I want in that the columns are right and values are right. But, what I need now is to know how to collapse the columns into one line. Like this...
stateName countyName Yr2001 Yr2002 Yr2003 Yr2004 Yr2005 ... etc
-------------------------- ---------- ---------- ---------- ---------- ---------
ALABAMA AUTAUGA COUNTY 28950 29700 29700 29700 ... etc, etc ...
Thanks everyone for help so far! Getting close.
select stateName,countyName,
case effectiveYear when '2001' then trhd1 else 'a' end as Yr2001,
case effectiveYear when '2002' then trhd1 else 'b' end as Yr2002,
case effectiveYear when '2003' then trhd1 else 'c' end as Yr2003,
case effectiveYear when '2004' then trhd1 else 'd' end as Yr2004,
case effectiveYear when '2005' then trhd1 else 'e' end as Yr2005,
case effectiveYear when '2006' then trhd1 else 'f' end as Yr2006,
case effectiveYear when '2007' then trhd1 else 'g' end as Yr2007,
case effectiveYear when '2008' then trhd1 else 'h' end as Yr2008
from std_GMTThresholds
order by statename, countyName
Here's my report...
stateName countyName Yr2001 Yr2002 Yr2003 Yr2004 Yr2005 Yr2006 Yr2007 Yr2008
--------------------------
ALABAMA AUTAUGA COUNTY 28950 b c d e f g h
ALABAMA AUTAUGA COUNTY a 29700 c d e f g h
ALABAMA AUTAUGA COUNTY a b 29700 d e f g h
ALABAMA AUTAUGA COUNTY a b c 29700 e f g h
... not showing the entire report ...
(Copy report to document and display as Courier New and columns will align.)
Notice that my ELSE places letters in the report to make it clear what line is being executed.
I am getting what I want in that the columns are right and values are right. But, what I need now is to know how to collapse the columns into one line. Like this...
stateName countyName Yr2001 Yr2002 Yr2003 Yr2004 Yr2005 ... etc
--------------------------
ALABAMA AUTAUGA COUNTY 28950 29700 29700 29700 ... etc, etc ...
Thanks everyone for help so far! Getting close.
I have not tested this but how about this:
select stateName,countyName,
case effectiveYear when '2001' then trhd1 else '' end as Yr2001,
case effectiveYear when '2002' then trhd1 else '' end as Yr2002,
case effectiveYear when '2003' then trhd1 else '' end as Yr2003,
case effectiveYear when '2004' then trhd1 else '' end as Yr2004,
case effectiveYear when '2005' then trhd1 else '' end as Yr2005,
case effectiveYear when '2006' then trhd1 else '' end as Yr2006,
case effectiveYear when '2007' then trhd1 else '' end as Yr2007,
case effectiveYear when '2008' then trhd1 else '' end as Yr2008
from std_GMTThresholds
group by stateName,countyName
order by statename, countyName
select stateName,countyName,
case effectiveYear when '2001' then trhd1 else '' end as Yr2001,
case effectiveYear when '2002' then trhd1 else '' end as Yr2002,
case effectiveYear when '2003' then trhd1 else '' end as Yr2003,
case effectiveYear when '2004' then trhd1 else '' end as Yr2004,
case effectiveYear when '2005' then trhd1 else '' end as Yr2005,
case effectiveYear when '2006' then trhd1 else '' end as Yr2006,
case effectiveYear when '2007' then trhd1 else '' end as Yr2007,
case effectiveYear when '2008' then trhd1 else '' end as Yr2008
from std_GMTThresholds
group by stateName,countyName
order by statename, countyName
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
awking00, thanks for your comments.
dwe761, your code works perfectly!! I'll analyze it so I understand. In the meantime, you've saved me hours of work. Thank you.
dwe761, your code works perfectly!! I'll analyze it so I understand. In the meantime, you've saved me hours of work. Thank you.
http://www.experts-exchang
http://www.experts-exchang
If using SQL Server 2005 or later, you have the option of using a PIVOT command. Take a look here:
2005:
http://msdn.microsoft.com/
2008:
http://msdn.microsoft.com/
If using Access, you can create a Crosstab query.
http://support.microsoft.c
I don't know if there is anything available in MySQL.