The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.

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?

asked 08 Nov '13, 14:19

sqlgeek's gravatar image

sqlgeek
1477713
accept rate: 0%

edited 12 Nov '13, 13:05

Mark%20Culp's gravatar image

Mark Culp
22.3k9129262


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:

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.

permanent link

answered 08 Nov '13, 18:06

Volker%20Barth's gravatar image

Volker Barth
29.3k287438644
accept rate: 32%

edited 09 Nov '13, 06:06

permanent link

answered 22 Nov '13, 09:10

mfkpie8's gravatar image

mfkpie8
86404550
accept rate: 10%

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
permanent link

answered 12 Nov '13, 12:57

Nica%20_SAP's gravatar image

Nica _SAP
866722
accept rate: 3%

edited 12 Nov '13, 13:12

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

(12 Nov '13, 13:07) Justin Willey
Replies hidden

Thanks for the clarification :)

(13 Nov '13, 05:39) Justin Willey
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×34
×8
×2

question asked: 08 Nov '13, 14:19

question was seen: 6,786 times

last updated: 22 Nov '13, 09:10