alt text

alt text

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

asked 16 Nov '13, 23:27

mfkpie8's gravatar image

mfkpie8
268656974
accept rate: 12%

edited 22 Nov '13, 14:36

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297

What is your question?

(17 Nov '13, 03:22) Mark Culp

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

(18 Nov '13, 00:33) mfkpie8

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:

alt text

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

alt text

(22 Nov '13, 14:18) Yufei Guo
Replies hidden

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

(22 Nov '13, 20:36) mfkpie8

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

permanent link

answered 21 Nov '13, 12:34

Yufei%20Guo's gravatar image

Yufei Guo
401213
accept rate: 6%

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

permanent link

answered 22 Nov '13, 14:44

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297
accept rate: 41%

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
下面那个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:

alt text Rows into columns

permanent link

answered 21 Nov '13, 21:12

mfkpie8's gravatar image

mfkpie8
268656974
accept rate: 12%

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

(22 Nov '13, 09:33) mfkpie8
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×13

question asked: 16 Nov '13, 23:27

question was seen: 4,977 times

last updated: 22 Nov '13, 20:39