Dynamic Rows into columns

[MarkCulp]

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;