troubleshooting Question

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

Avatar of DANNYMAC
DANNYMAC asked on
Sybase Database
6 Comments1 Solution4614 ViewsLast Modified:
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( AS CountOfcat
SELECT Table1.aType, Count( AS [Total Of cat]
FROM Table1
GROUP BY Table1.aType
PIVOT Format([aDate],"yyyy");

Can this be accomplished in Sybase?
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros