Prev: Number generator
Next: indexes and constraints
From: raja on 14 Dec 2009 07:54
Hi,

I have a table having values like below :

col1,col2,col3,col4
a, b, c, d
a, b, c, e
a, b, c, f

I need the output as follows :
a,
b,
c,
d,
e,
f

can anyone, Please help me in this, How can i do that ?

Thanks.

With Regards,
Raja.
From: pikes on 14 Dec 2009 08:57
On Dec 14, 7:54 am, raja <dextersu...(a)gmail.com> wrote:
> Hi,
>
> I have a table having values like below :
>
> col1,col2,col3,col4
> a,      b,      c,      d
> a,      b,      c,      e
> a,      b,      c,      f
>
> I need the output as follows :
> a,
> b,
> c,
> d,
> e,
> f
>
> can anyone, Please help me in this, How can i do that ?
>
> Thanks.
>
> With Regards,
> Raja.

One way I could see it done is like this:

select distinct tbl.c
from(
select col1 as c from tbl
union
select col2 as c from tbl
union
select col3 as c from tbl
union
select col4 as c from tbl) tbl

From: Plamen Ratchev on 14 Dec 2009 10:24
Here is one solution:

CREATE TABLE Foo (
keycol INT NOT NULL PRIMARY KEY,
col1 CHAR(1),
col2 CHAR(1),
col3 CHAR(1),
col4 CHAR(1));

INSERT INTO Foo VALUES(1, 'a', 'b', 'c', 'd');
INSERT INTO Foo VALUES(2, 'a', 'b', 'c', 'e');
INSERT INTO Foo VALUES(3, 'a', 'b', 'c', 'f');


SELECT DISTINCT value
FROM Foo
UNPIVOT
(value FOR col IN (col1, col2, col3, col4)) AS U;

/*

value
-----
a
b
c
d
e
f

*/

--
Plamen Ratchev
http://www.SQLStudio.com
 | 
Pages: 1
Prev: Number generator
Next: indexes and constraints