|
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 |
|
mfkpie8： 你好， 谢谢你提出的有关SQLAnywhere的问题.可是你的有些问题我们不完全理解.所以你是否能用中文提出你所有问题?我们尽量帮助你. 谢谢! Well, could you please translate your answer?
(21 Nov '13, 12:49)
Volker Barth
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!
(21 Nov '13, 12:52)
Chris Keating
Replies hidden
OK, I see that using a Chinese-English translator does not necessarily solve these language problems:)
(21 Nov '13, 16:46)
Volker Barth
|
|
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 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;
(22 Nov '13, 20:33)
mfkpie8
|
|
@Yufei Guo 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:
http://sqlanywhere.blogspot.com/2010/01/crosstab-rotate-pivot.html 是否可以写函数针对行转列的方法，写函数来处理这些？
(22 Nov '13, 09:33)
mfkpie8
|
Rows into columns
What is your question?
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
Here is his problem:
He has a table named BOM which contains the following columns
The table now contains the following rows:
Now he is looking for some SQL statements that can show the results as the following
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 如何能写一个函数，谢谢，行转列行式