Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

One column become multiple column display record 1

Status
Not open for further replies.

soushi01

Technical User
Dec 27, 2010
31
MY
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 [hammer] [hairpull]

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



Any advise ? [cry]

 
Why not simply this ?
SQL:
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
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top