Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    19

    Unanswered: For each user info, all the courses

    Hi,
    I am using Oracle 9i and its a daily job for user reports.
    I have requirements that needs to pull result sets which has user info repeated but for different course code and different sec access.
    I am joining multiple table and using multiple columns so i have not posted here table/insert script but need to know how can i handle this output in my sql query?

    Code:
    select distinct b.S_name "SName", d.login "Login",
    d.f_name "First Name",
    d.l_name "Last Name",
    d.email "Email",
    (SELECT d.login FROM user d WHERE d.id = c.rep_to_id) AS S_ID,
    (SELECT d.email FROM user d WHERE d.id = c.rep_to_id) AS S_EMAIL,
    e.desc "DEscription",
    f.wave "Wave",
    C_CODE, S_TASK ==> these two columns have multiple values for each user info
    FROM CT_E_STAK_MAP  	A,
              ct_stak 		B,
              e_info 		C,
              user 			D,
              e_code 		E,
              e_web_info 		F, 
              C_STAK_MAP 		G,
              C_CORS 		H, 
              C_S_TASK 		I,
              C_S_STAK_MAP 		J
    WHERE a.C_stak_id 	= b.c_stak_id 
    AND   b.c_stak_id 	= G.c_stak_id
    AND   B.C_STAK_ID 	= J.C_STAK_ID
    AND   J.C_S_TASK_ID 	= I.C_S_TASK_ID
    AND   G.C_CORS_ID      	= H.C_CORS_ID
    AND   a.U_id         	= c.U_id 
    AND   a.U_id         	= d.id 
    AND   d.id              = f.U_id 
    AND   c.ctll3 		= e.c_st_val 
    AND   c.ctl1_2 		= e.ctl1_2 
    AND   e.c_set_name 	= 'EY' 
    AND   c.active		= 1
    )
    order by s_name;
    For this query i am getting following results:
    Code:
    S_NAME	LOGIN	F_NAME	L_NAME	EMAIL	S_ID	S_EMAIL	DESC	EWAVE	C_Code	S_Task
    FTax	AY	MESAR	BOUEZ	MB@San.COM			0266-Mx	PI	CODE1	MGR
    FTax	AY	MESAR	BOUEZ	MB@San.COM			0266-Mx	PI	CODE1	User1
    FTax	AY	MESAR	BOUEZ	MB@San.COM			0266-Mx	PI	CODE1	USer2
    FTax	AY	MESAR	BOUEZ	MB@San.COM			0266-Mx	PI	CODE2	MGR
    FTax	AY	MESAR	BOUEZ	MB@San.COM			0266-Mx	PI	CODE2	USER1
    FTax	AY	MESAR	BOUEZ	MB@San.COM			0266-Mx	PI	CODE3	USER1
    FTax	AY	MESAR	BOUEZ	MB@San.COM			0266-Mx	PI	CODE3	USER2
    										
    FTax	PY	LUZ 	COTA	LC@San.COM	KC	KC@San.COM	0266-Mx	PI	CODE5	MGR
    FTax	PY	LUZ 	COTA	LC@San.COM	KC	KC@San.COM	0266-Mx	PI	CODE5	User1
    FTax	PY	LUZ 	COTA	LC@San.COM	KC	KC@San.COM	0266-Mx	PI	CODE5	User2
    										
    TOPS	BE	Ash	Dear	AD@San.COM	UE	UE@San.COM	TN12	PD	CODE6	MGR
    TOPS	BE	Ash	Dear	AD@San.COM	UE	UE@San.COM	TN12	PD	CODE6	User1
    TOPS	BE	Ash	Dear	AD@San.COM	UE	UE@San.COM	TN12	PD	CODE6	User2
    TOPS	BE	Ash	Dear	AD@San.COM	UE	UE@San.COM	TN12	PD	CODE7	USer1
    TOPS	BE	Ash	Dear	AD@San.COM	UE	UE@San.COM	TN12	PD	CODE7	USer2
    TOPS	BE	Ash	Dear	AD@San.COM	UE	UE@San.COM	TN12	PD	CODE7	User3
    but i would like output this way so C-CODE and S_TASK result will be in a one set with same user info.
    Desired output:
    Code:
    S_NAME	LOGIN	F_NAME	L_NAME	EMAIL	S_ID	S_EMAIL	DESC	EWAVE	C_Code	S_Task
    FTax	AY	MESAR	BOUEZ	MB@San.COM			0266-Mx	PI	CODE1, CODE2, CODE3	MGR, USer1, USer2,
    										
    FTax	PY	LUZ 	COTA	LC@San.COM	KC	KC@San.COM	0266-Mx	PI	CODE5	MGR, USer1, USer2,
    										
    TOPS	BE	Ash	Dear	AD@San.COM	UE	UE@San.COM	TN12	PD	CODE6, CODE7	MGR, USer1, USer2,
    										
    TOPS	BE	Ash	Dear	AD@San.COM	UE	UE@San.COM	TN12	PD	CODE6, CODE7	MGR, USer1, USer2, USer3
    So it will be grouping all the user info which is common and one row for each corrosponding C_CODE and S_TASK in either comma or other delimitted.

    I hope i explain properly.

    Thanks in advance!

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2004
    Posts
    19
    Thanks as you have seen but i have different requirement that user needs to C-CODE and S_TASK values bundle in comma or pipe delimitted.
    If oyu throw some lights, it will be appreciated.

    Thanks,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •