One column become multiple column display record 1

[soushi01]

Hi,

I need display something like this

Here is table User_test

USER_ID ---- NAME
1 ------------- ADAM
2 ------------- BABY
3 ------------- CATTY
4 ------------- DOGGY
5 ------------- ELLE
6 ------------- FLURRY

i want the record display like this
GroupA ----- GroupB -------GroupC
ADAM -------CATTY --------ELLE
BABY --------DOGGY --------FLURRY


but i try to write query like this

SELECT
(SELECT B.NAME FROM USER_TEST B WHERE B.NAME=A.NAME AND B.NAME IN ('ADAM','BABY') ) AS Group_A,
(SELECT C.NAME FROM USER_TEST C WHERE C.NAME=A.NAME AND C.NAME IN ('CATTY','DOGGY') ) AS Group_B,
(SELECT C.NAME FROM USER_TEST C WHERE C.NAME=A.NAME AND C.NAME IN ('ELLE','FLURRY') ) AS Group_C
FROM USER_TEST A


but it display like this

GroupA ------- GroupB -------GroupC
ADAM -------------------------------
BABY --------------------------------
---------------CATTY --------------
---------------DOGGY ---------------
--------------------------------ELLE
--------------------------------FLURRY



Any advise ?

 

[PHV]

Why not simply this ?

SELECT A.NAME AS GroupA, B.NAME AS GroupB, C.NAME AS GroupC
FROM USER_TEST A 
INNER JOIN USER_TEST B ON A.USER_ID = B.USER_ID-2
INNER JOIN USER_TEST C ON B.USER_ID = C.USER_ID-2
WHERE A.USER_ID IN(1,2)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[soushi01]

Hi, PHV

tq very much your solution