How to use custom functions to achieve row to column

[ximen]

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

[ximen]

It should be the one of the dynamic statement, and there will be no single quotes in the fixed column name -- run SQL:

    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;

how do I remove quotes around the column names?

'route1' > route1 'router2' > router2 'router3'> router3

[VolkerBarth]

AFAIK, it's not possible to use aliases with a dynamic list via an array of values, see my older (and unanswered) question.

As a workaround, you might need to build the list of possible columns beforehand and then use EXECUTE IMMEDIATE with a fixed "IN (value1 as column_name1, .. valueN as column_nameN)" list with according aliases.

[ximen]

how do I remove quotes around the column names? 'route1' >route1

-- run SQL:

    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;

will return

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

how do I remove quotes around the column names?

'route1' > route1 'router2' > router2 'router3'> router3

[chris_keating]

Use aliases:

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

[VolkerBarth]

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.

[ximen]

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.

[Former Member]

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

[VolkerBarth]

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

[VolkerBarth]

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

[chris_keating]

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

[ximen]

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