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.5k9129264


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.5k291441646
accept rate: 32%

edited 09 Nov '13, 06:06

permanent link

answered 22 Nov '13, 09:10

mfkpie8's gravatar image

mfkpie8
86414651
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,933 times

last updated: 22 Nov '13, 09:10