Dynamic Rows into columns

[ximen]

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

[MarkCulp]

What is your question?

[ximen]

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

[Former Member]

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

[ximen]

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 如何能写一个函数，谢谢，行转列行式

[MarkCulp]

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

[ximen]

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

DO

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;

[Former Member]

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

[VolkerBarth]

Well, could you please translate your answer?

[chris_keating]

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!

[VolkerBarth]

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

[ximen]

@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

[ximen]

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