【问题】
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 不支持有序集合，需要再专门造个排序字段，代码就显得很难懂。用集算器协助简单得多：
| A | |
| 1 | $select * from mytable |
| 2 | =A1.group(ColGroup) |
| 3 | =A2.conj([~.ColGroup,"group"]|~.conj([ColUser,"user"])) |
| 4 | =create(ColGroupUser,UserType).record(A3) |
A1：查询数据表
A2：对数据表按照 ColGroup 分组
A3：对序列做合并，生成最终序表要显示的记录
A4：创建空序表，用序列 A3 成员组成序表的新记录
集算器是结构化数据计算工具，可以通过 JDBC 接口与报表或 JAVA 集成，很适合解决此类问题，可参考Java 如何调用 SPL 脚本。
