Selecting multiple columns as one column

Dev Shed Forums Sponsor:

January 29th, 2008, 03:50 PM

Stormybot

Registered User

Join Date: Jan 2008

Posts: 11

Time spent in forums: 8 h 37 m 35 sec
Reputation 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):

Quote:

SELECT column1 as combinedlist, column2 as combinedlist FROM `myTable` WHERE column1='data1' OR column2='data1' ORDER BY combinedlist

I have also tried:

Quote:

SELECT (column1, column2) as combinedlist FROM `myTable` WHERE column1='data1' OR column2='data1' ORDER BY combinedlist

Am I even close?

January 29th, 2008, 03:57 PM

jcarouth

Never give up

Join Date: Sep 2005

Location: College Station, TX, USA

Posts: 2,625

Folding Points: 1251 Folding Title: Novice Folder

Time spent in forums: 1 Month 2 Weeks 12 h 56 m 2 sec
Reputation Power: 807

Code:

select concat(column1, ', ', column2) as combinedlist
from `myTable`
where 'data1' in(column1, column2)
order by combinedlist

__________________
Jeff Carouth's Homepage

January 29th, 2008, 04:01 PM

Stormybot

Registered User

Join Date: Jan 2008

Posts: 11

Time spent in forums: 8 h 37 m 35 sec
Reputation Power: 0

Quote:

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

January 29th, 2008, 04:04 PM

shammat

Contributing User

Join Date: Oct 2003

Location: Germany

Posts: 2,683

Time spent in forums: 1 Month 3 Weeks 3 Days 19 h 35 m 2 sec
Reputation Power: 284

Code:

SELECT column1
FROM myTable
UNION ALL
SELECT column2
FROM myTable

January 29th, 2008, 04:05 PM

jcarouth

Never give up

Join Date: Sep 2005

Location: College Station, TX, USA

Posts: 2,625

Time spent in forums: 1 Month 2 Weeks 12 h 56 m 2 sec
Reputation Power: 807

Code:

select column1 as name
from your_table

union

select column2
from your_table

order by name

January 29th, 2008, 04:07 PM

jcarouth

Never give up

Join Date: Sep 2005

Location: College Station, TX, USA

Posts: 2,625

Time spent in forums: 1 Month 2 Weeks 12 h 56 m 2 sec
Reputation Power: 807

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).

January 29th, 2008, 04:15 PM

Stormybot

Registered User

Join Date: Jan 2008

Posts: 11

Time spent in forums: 8 h 37 m 35 sec
Reputation Power: 0

That worked perfectly! Thank you everyone! And thanks jcarouth
for the explanation between UNION and UNION ALL. This forum is great.

October 26th, 2012, 11:04 PM

karnival800

Contributing User

Join Date: Oct 2012

Posts: 35

Time spent in forums: 4 h 15 m 8 sec
Reputation Power: 1

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

October 27th, 2012, 01:06 PM

r937

SQL Consultant

Join Date: Feb 2003

Location: Toronto Canada

Posts: 26,359

Time spent in forums: 3 Months 1 Week 2 Days 5 h 19 m 29 sec
Reputation Power: 4140

Quote:

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

__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book

Selecting multiple columns as one column

Developer Shed Advertisers and Affiliates