Question Re: Dynamic Rows into columns in Technology Q&A

Dynamic Rows into columns

ximen — Sat, 16 Nov 2013 23:27:05 GMT

JOIN CROSS table

Table for bom this is the source table

Pictures of the second to the fourth line is the result of crosstab I want: his watch on the basis of the results of production area horizontal display reports Rows into columns : iphone 5 based products processing center back to classify horizontally Please help thank you very much

Re: Dynamic Rows into columns

MarkCulp — Sun, 17 Nov 2013 03:22:55 GMT

What is your question?

Re: Dynamic Rows into columns

ximen — Mon, 18 Nov 2013 00:33:21 GMT

Table for bom this is the source table

Pictures of the second to the fourth line is the result of crosstab I want: his watch on the basis of the results of production area horizontal display reports

Re: Dynamic Rows into columns

Former Member — Thu, 21 Nov 2013 12:34:23 GMT

mfkpie8：你好，谢谢你提出的有关SQLAnywhere的问题.可是你的有些问题我们不完全理解.所以你是否能用中文提出你所有问题?我们尽量帮助你. 谢谢!

Re: Dynamic Rows into columns

VolkerBarth — Thu, 21 Nov 2013 12:49:37 GMT

Well, could you please translate your answer?

Re: Dynamic Rows into columns

chris_keating — Thu, 21 Nov 2013 12:52:27 GMT

using google translate:

Hello, thank you for the question asked about SQLAnywhere, but you some of the problems that we do not fully understand, so you can ask your Chinese all questions? We try to help you, thank you!

Re: Dynamic Rows into columns

VolkerBarth — Thu, 21 Nov 2013 16:46:07 GMT

OK, I see that using a Chinese-English translator does not necessarily solve these language problems:)

Re: Dynamic Rows into columns

ximen — Thu, 21 Nov 2013 21:12:22 GMT

@Yufei Guo
下面那个BOM(图片）是源表，我想把他做成交叉的等式的表格式如excel第一二行的。形式我应该如何写代码你可以理解为交叉表，我把生产区域放在了标题行里面.可以是动态的语句吗？

Below the BOM (picture) is the source table, I want him to make cross-tabular equation as excel first two rows. How should I write code form You can understand the crosstab, I put on a production area inside the header row.

Dynamic SQL code ? 1 2 3 4 5 6

  现有表
    BOM table
    product_name(主产品)    WO_bom(材料名称)      machining center(加工中心)
     IPhone 5               shell(手机外壳)          china-taiwan(中国台湾)
     iphone 5            mainbord(手机主板)          china-shenzhen(中国深圳)
     IPhone 5     blunt appliances(冲电器)           USA

    想要的表结构为：
    result for:

Rows into columns

Re: Dynamic Rows into columns

ximen — Fri, 22 Nov 2013 09:33:58 GMT

http://sqlanywhere.blogspot.com/2010/01/crosstab-rotate-pivot.html 是否可以写函数针对行转列的方法，写函数来处理这些？

Re: Dynamic Rows into columns

Former Member — Fri, 22 Nov 2013 15:18:12 GMT

Here is his problem:

He has a table named BOM which contains the following columns

( product_name, part_name, production_center, quantity )

The table now contains the following rows:

Now he is looking for some SQL statements that can show the results as the following

Re: Dynamic Rows into columns

MarkCulp — Fri, 22 Nov 2013 14:44:03 GMT

So what you want to do is to pivot the table into a different format. SQL Anywhere does not currently let you directly do this but you can do it manually by the strategic use of aggregate operations. For example:

select product_name,
   '' as demand_for_product,
   list( if production_center = 'china-taiwan' then part_name else '' endif, '' )
       as part_name_1,
   sum( if production_center = 'china-taiwan' then quantity else 0 endif ) as quantity_1,
   list( if production_center = 'china-shenzhen' then part_name else '' endif, '' )
       as part_name_2,
   sum( if production_center = 'china-shenzhen' then quantity else 0 endif ) as quantity_2,
   list( if production_center = 'usa' then part_name else '' endif, '' )
       as part_name_3,
   sum( if production_center = 'usa' then quantity else 0 endif ) as quantity_3
from BOM
group by product_name;

HTH

Re: Dynamic Rows into columns

ximen — Fri, 22 Nov 2013 20:33:09 GMT

Thank you for your reply, whether we can write a function to deal with the problem turn these rows column,Should be how to write afunction equation under the convenient after use How should I write my that function

FOr example:

BEGIN

DECLARE @sql LONG VARCHAR;

SET @sql = 'SELECT customer_id';

FOR f_fetch

AS c_fetch NO SCROLL CURSOR FOR

SELECT DISTINCT cash_type AS @c1

FROM cash

ORDER BY cash_type

FOR READ ONLY

SET @sql = STRING (

  @sql,

  ', SUM ( ( IF cash.cash_type = ''',

  @c1,

  ''' THEN 1 ELSE 0 ENDIF ) * act_amt ) AS "',

  @c1,

  '"' );

END FOR;

SET @sql = STRING (

@sql,

' INTO #t1 FROM cash GROUP BY customer_id' );

MESSAGE @sql TO CONSOLE;

EXECUTE IMMEDIATE @sql;

SELECT * FROM #t1 ORDER BY customer_id; -- pivot table

END;

Re: Dynamic Rows into columns

ximen — Fri, 22 Nov 2013 20:36:13 GMT

The transfer line column whether we can be written as a function to deal with this problem, otherwise with static well solve these problems, what's the difference between a function of writing 如何能写一个函数，谢谢，行转列行式