【问题】

how to move two column data to one column data

Hi,
I have users that belongs to groups. I need to make both data in one column and specify if this is group or user.

Source data in “mytable” are:

Code:

ColGroup ColUser

Group1 User1

Group1 User2

Group2 User3

Group2 User4

...

Note: list of groups and users is longer than this simple sample. Each user can belong only to one group (ColUser is PK in table). Groups can have multiple users.

Result of SQL should be:

Code:

ColGroupUser UserType

Group1 group

User1 user

User2 user

Group2 group

User3 user

User4 user

...

Note: first is Group1, then there are listed all users that belongs to Group1 in our sample there are User1 and User2. Then must be displayed Group2 and after that all users that belongs to Group2 that are User3 and User4.

Question: how to write such an SQL to move two columns to one column?

My system: DB2 9.5 Enterprise on Linux
Thanks,
Grofaty

别人的回答：

SELECT COALESCE(ColGroup, ColUser) AS "ColGroupUser"

 , CASE

 WHEN GROUPING(ColGroup) = 0 THEN

 'group'

 ELSE 'user'

 END AS "UserType"

 FROM grofaty.mytable

GROUP BY

 GROUPING SETS(ColGroup, ColUser)

ORDER BY

 MAX(ColGroup), GROUPING(ColGroup), MAX(ColUser)

【回答】

将分组数据拼成一列，这种用法在组织报表时很常见，但 SQL 不支持有序集合，需要再专门造个排序字段，代码就显得很难懂。用集算器协助简单得多：

A2：对数据表按照 ColGroup 分组

A3：对序列做合并，生成最终序表要显示的记录

A4：创建空序表，用序列 A3 成员组成序表的新记录

集算器是结构化数据计算工具，可以通过 JDBC 接口与报表或 JAVA 集成，很适合解决此类问题，可参考Java 如何调用 SPL 脚本。