Avatar of DANNYMAC
DANNYMAC 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?
Sybase Database

Avatar of undefined
Last Comment
grant300

8/22/2022 - Mon
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
ASKER
DANNYMAC

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

ASKER
DANNYMAC

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'
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
grant300

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
DANNYMAC

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