Pivot/unpivot in sybase iq

[Former Member]

does sybase IQ support PIVOT/UNPIVOT like SQL server?
I have record set like the following:

Date              Name       salary
---               ---        -----  
1/1/2012          jay          50
1/1/2012          ken          60
1/2/2012          ken          60
1/2/2012          jay          50
1/3/2012          jay          55
1/3/2012          lisa         80
....

I want to show the result set as following.

Date,      ken,   Jay,   Lisa 
 1/1/2012    60     50       0 
 1/2/2012    60     50       0 
 1/3/2012    0      55       80

Is that even possible?

[VolkerBarth · Accepted solution]

I can't tell for Sybase IQ, however as IQ uses SQL Anywhere as its front end, I guess the following FAQ might apply to IQ, too:

• How do I rotate a table?

To cite from Breck's answer there:

SQL Anywhere doesn't have the ability to rotate (crosstab, pivot, whatever) a table. What it does have is EXECUTE IMMEDIATE, plus the ability to code IF expressions just about anywhere in the SELECT statement. You can combine those two features to kludge a solution.

[ximen]

http://sqlanywhere.blogspot.com/2010/01/crosstab-rotate-pivot.html This may be the answer you need

[Hazeleena]

the syntax is hardcoding the name IN syntax. What if the name is dynamic data?

[VolkerBarth]

Are you asking for SAP IQ or SAP SQL Anywhere? or the latter, see this question.

[Former Member]

SQL PIVOT and UNPIVOT are not available in SAP IQ and SAP SQLAnywhere.

With SQL PIVOT construct, one can pivot the Salary table as following:

DBA.Salary is a base table:

Date    Name    Salary
1/1/2012    Jay 50
1/1/2012    Ken 60
1/2/2012    Ken 60
1/2/2012    Jay 50
1/3/2012    Jay 55
1/3/2012    Lisa    80
 SELECT newPivot.*
    FROM (SELECT Date, Name, Salary
        FROM DBA.Salary ) AS source
    PIVOT
    ( SUM(source.Salary)
      FOR source.Name IN (‘Ken’ AS KEN, ‘Jay’ AS JAY, ‘Lisa’ AS LISA)
    ) AS newPivot

Date    KEN JAY LISA
1/1/2012    60  50  0
1/2/2012    60  50  0
1/3/2012    0   55  80

[justin_willey]

just checking - PIVOT is available in Sybase IQ but not SQL Anywhere, is that right?

[justin_willey]

Thanks for the clarification 🙂