|
1.How to use custom functions to achieve row to column
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
|
|
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. 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 '17, 07:35)
mfkpie8
Replies hidden
Is pivot available for Ultralite (Android and iOS) in the version 17?
(13 Apr '17, 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 '17, 09:49)
Volker Barth
Don't know, the v17 docs do not tell that AFAIK...
(13 Apr '17, 09:50)
Volker Barth
Pivot is a SQL Anywhere Server feature. It is not available in UltraLite.
(13 Apr '17, 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 '17, 09:57)
mfkpie8
|
|
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 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.
(21 Nov '22, 03:49)
Volker Barth
|
|
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 2
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;
(18 Nov '22, 10:04)
Chris Keating
Comment Text Removed
|