SQL Query - Multiple columns into one column

Question

I've had a hunt round here and google and been un able to find anything.

I did come accross the CONCAT function, but as my company runs SQL Server 2008 thats not an option :(

I have a query that outputs multiple columns / rows and I want to disply the results all in one column.

Example:

SELECT client, owner, payer, status
FROM Main
WHERE status = 'Active'

Result:

client | owner | payer | status

1      |  2    |  3    | Active
10     |  11   | 12    | Active

What I Want it to look like

ColumnX  |
1
2
3 
Active
10
11
12
Active

Thanks in advance for your help and I hope that makes sense :)

Cheers, Michael

This is a bad idea. Without a key you will have no idea what these values mean. You will have no sort order. — Isaac Fife
– Isaac Fife, Commented Aug 24, 2012 at 0:01
I'm curious about what you're trying to do with this query. Perhaps there's a better way to accomplish your goal. — Jim
– Jim, Commented Aug 24, 2012 at 0:02
thanks thanks for the quick reply. just trying to get a list of numbers that are 'active' it doesn't matter where they come from. will try the below when I'm back.at my desk! — Mikey
– Mikey, Commented Aug 24, 2012 at 0:30
As below, UNIONALL Method worked a charm! Thanks for the help all! — Mikey
– Mikey, Commented Aug 24, 2012 at 1:46

Taryn · Accepted Answer · 2012-08-24 00:09:30Z

10

What you are referring to is known as an UNPIVOT. This takes the columns and converts them to rows:

SELECT value, field
FROM
(
  select cast(client as varchar(10)) client, 
    cast(owner as varchar(10)) owner, 
    cast(payer as varchar(10)) payer, 
    status
  from main
  where status = 'Active'
) x
unpivot
(
  value 
  for field in (client, owner, payer, status)
) u

See SQL Fiddle with Demo

The UNPIVOT will give you the list of values in one column and then the column it came from in the other.

The thing that is required with an UNPIVOT is that all fields be of the same datatype, so you will need to perform a conversion of the datatypes so they can be presented in the same column.

You could also use a UNION ALL to perform this operation but again you will need to cast() the datatypes to be the same:

SELECT cast(client as varchar(10)) value
FROM main
WHERE status = 'active'
UNION ALL
SELECT cast(owner as varchar(10)) value
FROM main
WHERE status = 'active'
UNION All
SELECT cast(payer as varchar(10)) value
FROM main
WHERE status = 'active'
UNION ALL
SELECT status
FROM main
WHERE status = 'active'

See SQL Fiddle with Demo

edited Aug 24, 2012 at 0:09

answered Aug 24, 2012 at 0:04

Taryn

249k57 gold badges374 silver badges409 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aaron Bertrand Over a year ago

Sure, but you rolled it back. :-)

Taryn Over a year ago

@AaronBertrand I think cause I was editing it when you were too. Reason why we need to lock posts while editing. :)

HenryZhang · Accepted Answer · 2012-08-24 00:02:06Z

1

Have you taken a look at SQL Pivot? http://msdn.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

To my knowledge, you would need to UnPivot your table, which would produce a massive stack.

answered Aug 24, 2012 at 0:02

HenryZhang

1,3688 silver badges11 bronze badges

Collectives™ on Stack Overflow

SQL Query - Multiple columns into one column

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related