• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1858
  • Last Modified:

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
0
LarryAndro
Asked:
LarryAndro
  • 5
  • 4
  • 2
3 Solutions
 
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
 
awking00Commented:
See attached.
entries.txt
0
 
LarryAndroAuthor Commented:
Great pointers!  Let me peruse and experiment.  I'll be back...
0
Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
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
 
awking00Commented:
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
 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now