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?
 
dwe761Connect With a Mentor Software 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
 
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
 
awking00Connect With a Mentor Commented:
See attached.
entries.txt
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
LarryAndroAuthor Commented:
Great pointers!  Let me peruse and experiment.  I'll be back...
0
 
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
 
awking00Connect With a Mentor Commented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.