1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

How to combine two rows

Discussion in 'SQL PL/SQL' started by Farshad Javadi, Mar 18, 2014.

  1. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hello Gentelmen,

    I have the following simple query that produces output. Some shops have both Managers and Supervisros, but some other shops have only Managers or only Supervisors. Also some sohps have neither Manager nor Supervisor. For those shops that have both Managers and Supervisors, I like to see the "Manager" and "Supervisor" names on the same row (without duplicate rows) and not to have two rows one containing only Manager name and the other Supervisor name. I also need to see those rows containing Only Managers (wilh Null Supervisors) or Only Supervisors (with Null Managers) or Without Managers and Supervisorrs (Nulls for both). What can I do please?

    With Great Respect,
    Farshad


    Select Distinct
    TableName.FCC As FCC
    ,TableName.OFFICETYPE As OfficeType
    ,TableName.LOCATIONCODE As LocCode
    ,TableName.CITY As City
    ,TableName.SHOPNAME As ShopName
    ,TableName.groupname As groupname
    ,TableName.firstname As firstname
    ,TableName.lastname As lastname
    ,Case When (groupname Like'%MANAGER%') Then lastname||', '||firstname End AS MANAGER
    ,Case When (groupname Like'%SUPERVISOR%') Then lastname||', '||firstname End AS SUPERVISOR
    From TableName
    Order By
    FCC
    ,CITY
    ,LOCATIONCODE
    ,DATEAPTVALID
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    A self-join is what you need here so you can select Manager from one 'side' and Supervisor from the other. You would change the query to select MANAGER from Tablename (as tn1) and SUPERVISOR from Tablename (as tn2):


    select fcc, officetype, loccode, city, shopname, groupname, firstname, lastname, manager, supervisor, dateaptvalid
    from
    (Select Distinct
    TableName.FCC As FCC
    ,TableName.OFFICETYPE As OfficeType
    ,TableName.LOCATIONCODE As LocCode
    ,TableName.CITY As City
    ,TableName.SHOPNAME As ShopName
    ,TableName.groupname As groupname
    ,TableName.firstname As firstname
    ,TableName.lastname As lastname
    ,Case When (groupname Like'%MANAGER%') Then lastname||', '||firstname End AS MANAGER
    ,null AS SUPERVISOR
    ,dateaptvalid
    From TableName tn1 join
    (Select Distinct
    TableName.FCC As FCC
    ,TableName.OFFICETYPE As OfficeType
    ,TableName.LOCATIONCODE As LocCode
    ,TableName.CITY As City
    ,TableName.SHOPNAME As ShopName
    ,TableName.groupname As groupname
    ,TableName.firstname As firstname
    ,TableName.lastname As lastname
    ,null AS MANAGER
    ,Case When (groupname Like'%SUPERVISOR%') Then lastname||', '||firstname End AS SUPERVISOR
    , dateaptvalid
    From TableName) TN2 ON
    (tn2.fcc = tn1.fcc))
    Order By
    FCC
    ,CITY
    ,LOCATIONCODE
    ,DATEAPTVALID;


    See if that works for you as I don't have your table or data to work from.
     
    Farshad Javadi likes this.
  3. Farshad Javadi

    Farshad Javadi Active Member

    Messages:
    20
    Likes Received:
    0
    Trophy Points:
    80
    Hello David,

    Thanks for the query you sent me. This produces four lines for the shops with both Managers and Supervisors like:

    Manager-Name Supervisor-Name
    Manager-Name Null
    Null Supervisor-Name
    Null Null
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,351
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    As I do not know how your table is defined you will need to choose the proper join column/columns to prevent a Cartesian product.