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.

On Dec 14, 7:54*am, raja <dextersu... (AT) gmail (DOT) com> wrote:
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

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