1.How to use custom functions to achieve row to column

Blockquote

create table work_order_wo--create table
(

name nvarchar(20) not null,
name1 nvarchar(20) not null,
router1name2 nvarchar(20) not null
)
--insert data
insert work_order_wo values ('lucy','router1','polish');
insert work_order_wo values ('lucy','router2','polish2');
insert work_order_wo values ('lucy','router3','polish3');
insert work_order_wo values ('lili','router1','stamping');
insert work_order_wo values ('lili','router2','stamping2');
insert work_order_wo values ('mike','router1','stamping2');

select * from work_order_wo


How to use custom functions to achieve row to column
results:
name     route1    router2   router3 
lucy     polish    polish2   polish3
lili    stamping   stamping2
mike    stamping2

asked 13 Apr, 01:34

mfkpie8's gravatar image

mfkpie8
86424852
accept rate: 10%

edited 13 Apr, 01:36


With SQL Anywhere 17, you can use the new PIVOT clause, such as:

select * 
from
   (select * from work_order_wo) pv_data_source
   pivot
      (min(router1name2) 
      for name1 in ('router1', 'router2', 'router3')) pivoted_data
order by 2;

will return

name      router1   router2   router3
lucy      polish    polish2   polish3
lili      stamping  stamping2        
mike      stamping2 

Note, I used "order by 2" because it matches your desired result set...

The pivot uses the min aggregate because you do not really want to aggregate here but the PIVOT clause needs one aggregate function, so min() is uncritical.

If there might be other entries for column "name1", you will want to switch to a dynamic list. AFAIK, this must be done w.t.h. of an array variable because a simple "in (subquery)" does not work here - cf. that other FAQ -, such as:

create variable @arrName1 nvarchar(20) array;
-- array_agg must use dinstinct and should be filled ordered by the entries,
-- otherwise the columns in the pivoted output might be in an undesired order
set @arrName1 =
   (select array_agg(distinct name1 order by name1) from work_order_wo);
-- select * from unnest(@arrName1) dt(name1); -- check array contents
select * 
from
   (select * from work_order_wo) pv_data_source
   pivot
      (min(router1name2) 
      for name1 in @arrName1) pivoted_data
order by 2;

This will return the same result set.

permanent link

answered 13 Apr, 05:03

Volker%20Barth's gravatar image

Volker Barth
30.6k305455662
accept rate: 32%

edited 13 Apr, 06:01

Thank you for your message, but I use the version is sybase any where 12, would like to use the view to call the custom function. Similar to the "pivot" dynamic list, do not want to use static to achieve the results.

(13 Apr, 07:35) mfkpie8
Replies hidden

Is pivot available for Ultralite (Android and iOS) in the version 17?

(13 Apr, 09:33) Alex
Replies hidden
1

Look in this forum for questions tagged with "pivot" - as the PIVOT clause is new, there are several answers how to simulate that with dynamic SQL (EXECUTE IMMEDIATE), and some of the questions seem to be your own...

(13 Apr, 09:49) Volker Barth

Don't know, the v17 docs do not tell that AFAIK...

(13 Apr, 09:50) Volker Barth

Pivot is a SQL Anywhere Server feature. It is not available in UltraLite.

(13 Apr, 10:41) Chris Keating

thanks for the reply. At present my request is to use the dynamic view to display the crosstab, the current statement is not written to support the view, the view can only support the function

(14 Apr, 09:57) mfkpie8
showing 2 of 6 show all flat view
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:

×9

question asked: 13 Apr, 01:34

question was seen: 114 times

last updated: 14 Apr, 09:57