Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Arrow Unanswered: SQL question: 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
    Last edited by grofaty; 04-01-08 at 13:50.

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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   ...
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Jan 2003
    Posts
    1,605
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    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.
    ---
    "It does not work" is not a valid problem statement.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    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.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    never mind, i found out, db2 sorts NULLs last

    okay, replace NULL in my solution with an empty string

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  8. #8
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Right... that's even easier. It's getting late and I should be going home now.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.
    Last edited by tonkuma; 04-04-08 at 00:59.

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    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.

  11. #11
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    deleted because of double posts.
    Last edited by tonkuma; 04-04-08 at 01:35. Reason: deleted because of double posts.

  12. #12
    Join Date
    Jan 2003
    Posts
    1,605
    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

  13. #13
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    grofaty, next time you post a question, i will not be among those offering you any assistance
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •