SQL question: how to move two column data to one column data

grofaty · #1 (**permalink**) 04-01-08, 09:39

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

stolze · #2 (**permalink**) 04-01-08, 10:10

It violates normalization rules, so you should think carefully whether this makes sense.

Code:

SELECT DISTINCT colGroup AS colGroupUser, 'group' AS userType
FROM   ...
UNION ALL
SELECT DISTINCT colUser, 'user'
FROM   ...

grofaty · #3 (**permalink**) 04-01-08, 12:57

Stolze,
data model in table doesn't violates normalization rules. SQL to get data would like to have group-user dependency.

Your solution is not what I would like to get. Your SQL returns:

Code:

User1                user
User2                user
User3                user
User4                user
Group1               group
Group2               group

But I would like to have dependency. First group then all users that belongs to group:
Group1 --> this is first group
User1 --> this user belongs to Group1
User2 --> this user belongs to Group1
Group2 --> this is second group
User3 --> this user belongs to Group2
User4 --> this user belongs to Group2

Regards,
Grofaty

n_i · #4 (**permalink**) 04-01-08, 13:29

Code:

select name, type from (
SELECT DISTINCT colGroup AS name , 'group' AS type, colgroup as sortColumn
FROM   ...
UNION ALL
SELECT DISTINCT colUser as name, 'user' as type, colGroup || 'Z' as sortColumn
FROM   ...) t
order by sortColumn

I did not test it, but something along these lines should work.

stolze · #5 (**permalink**) 04-01-08, 13:36

Let me get this straight: you are mixing different information (user names vs. group names) and even use an identifier column to separate both - and then you claim that this is normalized? Rather questionable if you ask me... Also, a query returns a table. A table is a set of rows. And a set is not ordered by definition. From that perspective, your request does not make much sense generally. Or how do you know what would be the first, the second, ... group/user?

What you need instead, is a column that assigns some ordering criteria that you can use in a cursor via the ORDER BY clause. Going back to my simple UNION ALL operator, you could do this:

Code:

WITH groups
   AS ( SELECT DISTINCT colGroup AS colGroupUser, 'group' AS userType
        FROM   ... ),
   users AS (
      SELECT DISTINCT colUser, 'user'
      FROM   ... )
SELECT g.*, colGroupUser AS o
FROM   groups AS g
UNION ALL
SELECT u.*, ( SELECT colGroup || u.colGroupUser
              FROM ... AS x
              WHERE  x.colGroup = u.colGroupUser )
FROM   users AS u
ORDER BY o

You may have to cast the orders in the "o" column to CHAR to get padding into the picture and provide a stable sorting.

r937 · #6 (**permalink**) 04-01-08, 13:56

Code:

SELECT colUser  AS display_this
     , colGroup AS do_not_display__sortkey1 
     , colUser  AS do_not_display__sortkey2
  FROM mytable
UNION ALL
SELECT DISTINCT 
       colGroup 
     , colGroup 
     , NULL  
  FROM mytable
ORDER
    BY 2,3

i forget, does DB2 sort NULLs first or last?

r937 · #7 (**permalink**) 04-01-08, 13:58

never mind, i found out, db2 sorts NULLs last

okay, replace NULL in my solution with an empty string

stolze · #8 (**permalink**) 04-01-08, 14:06

Right... that's even easier. It's getting late and I should be going home now.

tonkuma · #9 (**permalink**) 04-03-08, 23:56

An example:

Code:

------------------------------ Commands Entered ------------------------------
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)
;
------------------------------------------------------------------------------

ColGroupUser UserType
------------ --------
Group1       group   
User1        user    
User2        user    
Group2       group   
User3        user    
User4        user    

  6 record(s) selected.

tonkuma · #10 (**permalink**) 04-04-08, 00:24

Quote:

Originally Posted by r937

Code:

SELECT colUser  AS display_this
     , colGroup AS do_not_display__sortkey1 
     , colUser  AS do_not_display__sortkey2
  FROM mytable
UNION ALL
SELECT DISTINCT 
       colGroup 
     , colGroup 
     , NULL  
  FROM mytable
ORDER
    BY 2,3

i forget, does DB2 sort NULLs first or last?

Although, you don't want to display do_not_display__sortkey1 and do_not_display__sortkey2, they are displayed.
To suppress displaying them, you shoud put the query in subquery and specify only columns you want to display in outmost SELECT list.
Another issue is to name the columns of UNIONed selects, you should use same name in all UNIONed selects or name them after correlation name(S in the following example). This syntax is different from Oracle's one.

Code:

------------------------------ Commands Entered ------------------------------
SELECT display_this AS "ColGroupUser"
     , "UserType"
FROM (
SELECT colUser  AS display_this
     , colGroup AS do_not_display__sortkey1 
     , colUser  AS do_not_display__sortkey2
     , 'user'   AS "UserType"
  FROM grofaty
UNION ALL
SELECT DISTINCT 
       colGroup AS display_this
     , colGroup AS do_not_display__sortkey1 
     , ''       AS do_not_display__sortkey2
     , 'group'  AS "UserType"
  FROM grofaty
) S
ORDER BY
      do_not_display__sortkey1
    , do_not_display__sortkey2;
------------------------------------------------------------------------------

ColGroupUser UserType
------------ --------
Group1       group   
User1        user    
User2        user    
Group2       group   
User3        user    
User4        user    

  6 record(s) selected.

tonkuma · #11 (**permalink**) 04-04-08, 00:33

deleted because of double posts.

grofaty · #12 (**permalink**) 04-04-08, 05:02

Quote:

Originally Posted by stolze

Let me get this straight: you are mixing different information (user names vs. group names) and even use an identifier column to separate both - and then you claim that this is normalized? Rather questionable if you ask me... Also, a query returns a table. A table is a set of rows. And a set is not ordered by definition. From that perspective, your request does not make much sense generally. Or how do you know what would be the first, the second, ... group/user?

Hi,
I was not clear enough. My data in my source table is normalized. This is just a sample of data to dramatically reduce complexity of my real data. Thanks for tips, it helped me think in more deep to get solution.

SQL returning data is just a report that has to be made in this way - end user requirement.

Ordering sequence I have solved by ordering by users_id which is integer column. Just trying to say this was just a sample data...

I have solved the problem now. Thanks for ideas, it helped me much.
Thanks for help,
Grofaty

r937 · #13 (**permalink**) 04-04-08, 09:07

grofaty, next time you post a question, i will not be among those offering you any assistance

Internet Services

Web Development

Digital Marketing

Consumer Tech