Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

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
24.9k10141297


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
40.2k361550822
accept rate: 34%

edited 09 Nov '13, 06:06

permanent link

answered 22 Nov '13, 09:10

mfkpie8's gravatar image

mfkpie8
273667075
accept rate: 12%

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:

×41
×13
×2

question asked: 08 Nov '13, 14:19

question was seen: 20,685 times

last updated: 22 Nov '13, 09:10