asked on

How Do I create pivot query in t-sql for I-Anywhere

I would liek to create a result set like the one below from Access but need to do this in SQL for I -anywhere sybase.
Given table ( Top row column headers)
aType(varchar)      aDate(Short datetime)      Cat ( Varchar)
A      1/1/2008      IND
B      2/1/2008      ARI
c      3/1/2008      ATL
A      4/1/2008      BAL
B      5/1/2008      BUF
c      6/1/2008      CAR
A      7/1/2008      CHI
B      8/1/2008      CIN
c      9/1/2008      CLE
A      10/1/2008      DAL
B      11/1/2008      DEN
c      12/1/2008      DET
A      1/1/2009      GB
B      2/1/2009      HOU
c      3/1/2009      IND
A      4/1/2009      JAX
B      5/1/2009      KC
c      6/1/2009      MIA
A      7/1/2009      MIN
B      8/1/2009      NE
c      9/1/2009      NO
A      10/1/2009      NYG
B      11/1/2009      NYJ
c      12/1/2009      OAK
A      1/1/2010      PHI
B      2/1/2010      PIT
c      3/1/2010      SD
A      4/1/2010      SEA
B      5/1/2010      SF
c      6/1/2010      STL
A      7/1/2010      TB
B      8/1/2010      TEN
c      9/1/2010      WAS

I would like to roll up the data as a pivot query with the results below( From MS Access crosstab query)
aType      TotalOfcat      2008      2009      2010
A      11      4      4      3
B      11      4      4      3
c      11      4      4      3
sql from Access

TRANSFORM Count(Table1.cat) AS CountOfcat
SELECT Table1.aType, Count(Table1.cat) AS [Total Of cat]
FROM Table1
GROUP BY Table1.aType
PIVOT Format([aDate],"yyyy");

Can this be accomplished in Sybase?

grant300

Yes. There are two approaches:
1) You can use the CASE statement to get the values into the correct columns and then sum() and group by
2) You can create a pivot table. This technique has grown less popular since the introduction of the CASE statement to most SQL implementations, however; it can be used to do this kind of thing dynamically versus the static nature of the CASE syntax.

Using the case statement, you query looks roughly like:

SELECT aType, COUNT(cat) as 'Total of cat',
sum(case when datepart('yyyy',aDate) = 2008 then 1 else 0 end) as '2008',
sum(case when datepart('yyyy',aDate) = 2009 then 1 else 0 end) as '2009',
sum(case when datepart('yyyy',aDate) = 2010 then 1 else 0 end) as '2010'
FROM Table1
GROUP BY aType

Regards,
Bill

DANNYMAC

ASKER

Bill,
This is close and I came up with this myself but what I need to do is piviot on the Adate field as the years can be any date since 1952 but looking at a 3 year or 5 year spread.
So the goal is to have the Adate field define the column header.
Something like the code snippet provided.

Note 1 - I have to dynamically build the column name, This is what I can not figure out. I tried the code in the first case statement but that will fail. I have just included it for the example. In Access I am able to Pivot on the aDate value to create a column name.

Note 2- DatePart Abbreviation does not require quotes, use literal YYYY or YEAR

Declare @bdate datetime
Set  @bdate =  CONVERT(DATETIME, '2010-12-01 00:00:00', 102) -- Any passed in date
 
SELECT aType, COUNT(cat) as 'Total of cat',
              sum(case when datepart(Year,aDate) = datepart(year,@bDate) then 1 else 0 end) as datepart(year,dateadd(yy,-1,@bDate)),
              sum(case when datepart(Year,aDate) = datepart(year,dateadd(yy,-1,@bDate)) then 1 else 0 end) as '2009',
              sum(case when datepart(YYYY,aDate) = datepart(year,dateadd(yy,-2,@bDate)) then 1 else 0 end) as '2008'
  FROM Table1
Where Adate between dateadd(yy,-2, @bDate) and @bDate  
GROUP BY aType

Open in new window

DANNYMAC

ASKER

Me error in the post:
Line 5 should be
sum(case when datepart(Year,aDate) = datepart(year,@bDate) then 1 else 0 end) as datepart(year,@bDate),
For the current year of bDate paramater. The result for this example should be '2010'

ASKER CERTIFIED SOLUTION

grant300

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

DANNYMAC

ASKER

Bill, Sorry for the slow response.
the Execute statement works a little different in sybase Ianywhere 8. So I guess I wil had code column name as generic labels.
Execute will only execute a stored procedure. So I would need a report to call a procedure to call a procedure, and that is just a maybe. Here is what I get with an execute statement :
Code:

declare @str varchar(2)
set @str = 'A' + 'B'
Print @str
exec @str
Isql Error:
Could not execute statement
ASA Error -265:Procedure '@str' not found

grant300

Check the syntax on the execute statement again. There are two additional usages for the EXECUTE IMMEDIATE statement

EXECUTE IMMEDIATE string-expression
or
EXECUTE (string-expression)

I was very specific with the syntax for this vary reason. We have all learned this one the hard way at some point.

> After you build up the @CMDSTR, you use the Execute Immediate function, execute (@CMDSTR), ...

Regards,
Bill