Link to home
Start Free TrialLog in
Avatar of LarryAndro
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,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
Avatar of dwe761
dwe761
Flag of United States of America image

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.
SOLUTION
Avatar of awking00
awking00
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of LarryAndro
LarryAndro

ASKER

Great pointers!  Let me peruse and experiment.  I'll be back...
awking00,
Is your solution for an Oracle database?
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!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.