【Question】

How to move two columns of data to one column data?
I have users that belong to groups. I need to make both data in one column and specify if this is group or user.

Source data in “mytable”:

ColGroup ColUser

 

Group1 User1

 

Group1 User2

 

Group2 User3

 

Group2 User4

 

...

 

Note: The 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:

ColGroupUser UserType

 

Group1 group

 

User1 user

 

User2 user

 

Group2 group

 

User3 user

 

User4 user

 

...

Note: Start with Group1, then list all users that belong to Group1. In our sample there are User1 and User2. Then move on to display Group2 and all users that belong to Group2, which are User3 and User4 here.

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

My system: DB2 9.5 Enterprise on Linux
A solution:

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)

 

【Answer】

It’s common to display multiple columns of grouped data with one column in report building. Sine SQL doesn’t support ordered sets, we need to create a sequence number field, which makes the code difficult to write and understand. It will become easy if we perform the data combination in esProc SPL (Structured Process Language):

	A
1	$select * from mytable
2	=A1.group(ColGroup)
3	=A2.conj([~.ColGroup,"group"]|~.conj([ColUser,"user"]))
4	=create(ColGroupUser,UserType).record(A3)

A1: Retrieve data from mytable.

A2: Group records by ColGroup.

A3: Union members of the sequence to generate a new one with expected members.

 

A4: Create an empty table sequence and populate A3’s members in order row by row.

esProc is intended to process structured data and can be integrated with a reporting tool or a Java application via JDBC. See How to Call an SPL Script in Java.