#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2008
    Posts
    11
    Rep Power
    0

    Selecting multiple columns as one column


    Hi everyone. First time posting. I did a search and could not find what I was looking for. Hopefully someone can answer this.

    I am trying to SELECT multiple columns as one column for display. Something like this (which obviously does not work):

    SELECT column1 as combinedlist, column2 as combinedlist FROM `myTable` WHERE column1='data1' OR column2='data1' ORDER BY combinedlist
    I have also tried:

    SELECT (column1, column2) as combinedlist FROM `myTable` WHERE column1='data1' OR column2='data1' ORDER BY combinedlist
    Am I even close?
  2. #2
  3. Never give up
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Sep 2005
    Location
    College Station, TX, USA
    Posts
    2,625
    Rep Power
    808
    Code:
    select concat(column1, ', ', column2) as combinedlist
    from `myTable`
    where 'data1' in(column1, column2)
    order by combinedlist
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2008
    Posts
    11
    Rep Power
    0
    Originally Posted by jcarouth
    Code:
    select concat(column1, ', ', column2) as combinedlist
    from `myTable`
    where 'data1' in(column1, column2)
    order by combinedlist
    Sorry, I should have explained better. I do not actually want it listed as "column1, column2" in the results. Maybe an example would be good.

    Say column1 had:
    Bob
    Steve
    Jen
    Valerie

    Say column2 had:
    David
    Kevin
    Miko
    Walt

    I want my resulting combinedlist to have:
    Bob
    David
    Jen
    Kevin
    Miko
    Steve
    Valerie
    Walt
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Oct 2003
    Location
    Germany
    Posts
    2,785
    Rep Power
    348
    Code:
    SELECT column1
    FROM myTable
    UNION ALL
    SELECT column2
    FROM myTable
  8. #5
  9. Never give up
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Sep 2005
    Location
    College Station, TX, USA
    Posts
    2,625
    Rep Power
    808
    Code:
    select column1 as name
    from your_table
    
    union
    
    select column2
    from your_table
    
    order by name
  10. #6
  11. Never give up
    Devshed Frequenter (2500 - 2999 posts)

    Join Date
    Sep 2005
    Location
    College Station, TX, USA
    Posts
    2,625
    Rep Power
    808
    note that UNION vs UNION ALL will give you no duplicates for the former and duplicates for the latter (e.g. if David is in both column1 and column2 then using UNION you will have only one 'David' in the results whereas UNION ALL will give you two or more).
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jan 2008
    Posts
    11
    Rep Power
    0
    That worked perfectly! Thank you everyone! And thanks jcarouth
    for the explanation between UNION and UNION ALL. This forum is great.
  14. #8
  15. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2012
    Posts
    35
    Rep Power
    2

    a question on counting unions


    lets say i wanted to COUNT total occurrences in multiple columns how would i do that?

    for example

    Col1
    David
    Joe
    Mary
    David

    Col2
    Chris
    Jack
    Joe

    to make this
    David 2
    Joe 2
    Mary 1
    Chris 1
    Jack 1
  16. #9
  17. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by karnival800
    lets say i wanted to COUNT total occurrences in multiple columns how would i do that?
    Code:
    SELECT name
         , COUNT(*)
      FROM ( SELECT column1 AS name
               FROM daTable
             UNION ALL
             SELECT column2
               FROM daTable ) AS u
    GROUP
        BY name
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

IMN logo majestic logo threadwatch logo seochat tools logo