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,countyName,FIPS,TRHD1

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
LVL 1
LarryAndroAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

dwe761Software EngineerCommented:
Check here:
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20563919.html
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_20643780.html
 
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/en-us/library/ms177410(SQL.90).aspx
2008:
http://msdn.microsoft.com/en-us/library/ms177410.aspx
 
If using Access, you can create a Crosstab query.
http://support.microsoft.com/kb/304458
 
I don't know if there is anything available in MySQL.
0
awking00Information Technology SpecialistCommented:
See attached.
entries.txt
0
LarryAndroAuthor Commented:
Great pointers!  Let me peruse and experiment.  I'll be back...
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

dwe761Software EngineerCommented:
awking00,
Is your solution for an Oracle database?
0
LarryAndroAuthor Commented:
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!
0
awking00Information Technology SpecialistCommented:
Yes. I saw the question in the PL/SQL zone and just assumed it was Oracle without looking at the other zones or tags. Sorry. It seems that an awful lot of questions are posted in the PL/SQL zone that are not for Oracle dbms. Maybe the EE folks could add a hint when people select that zone.
0
LarryAndroAuthor Commented:
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.
0
LarryAndroAuthor Commented:
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.
0
dwe761Software EngineerCommented:
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
0
dwe761Software EngineerCommented:
Or rather, this:

select a.stateName,a.countyName,
sum(a.Yr2001) As Yr2001,
sum(a.Yr2002) As Yr2002,
sum(a.Yr2003) As Yr2003,
sum(a.Yr2004) As Yr2004,
sum(a.Yr2005) As Yr2005,
sum(a.Yr2006) As Yr2006,
sum(a.Yr2007) As Yr2007,
sum(a.Yr2008) As Yr2008
FROM
(
select stateName,countyName,
case effectiveYear when '2001' then trhd1 else 0 end as Yr2001,
case effectiveYear when '2002' then trhd1 else 0 end as Yr2002,
case effectiveYear when '2003' then trhd1 else 0 end as Yr2003,
case effectiveYear when '2004' then trhd1 else 0 end as Yr2004,
case effectiveYear when '2005' then trhd1 else 0 end as Yr2005,
case effectiveYear when '2006' then trhd1 else 0 end as Yr2006,
case effectiveYear when '2007' then trhd1 else 0 end as Yr2007,
case effectiveYear when '2008' then trhd1 else 0 end as Yr2008
from std_GMTThresholds
) a
group by stateName,countyName
order by statename, countyName  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LarryAndroAuthor Commented:
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.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.