Pivot/unpivot in sybase iq

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?

pivot sybase-iq unpivot

asked 08 Nov '13, 14:19

sqlgeek
147●7●7●13
accept rate: 0%

edited 12 Nov '13, 13:05

Mark Culp
24.9k●10●141●297

flat view

3 Answers:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 08 Nov '13, 18:06

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 09 Nov '13, 06:06

flat view

1	http://sqlanywhere.blogspot.com/2010/01/crosstab-rotate-pivot.html This may be the answer you need permanent link answered 22 Nov '13, 09:10 mfkpie8 273●66●70●75 accept rate: 12% flat view

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 _SAP
866●7●22
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

flat view

Your answer

toggle preview

community wiki:

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

learn more about Markdown

Question tags:

sybase-iq ×41
pivot ×13
unpivot ×2

question asked: 08 Nov '13, 14:19

question was seen: 20,681 times

last updated: 22 Nov '13, 09:10

SAP SQL Anywhere

Pivot/unpivot in sybase iq

Follow this question

Related questions