concatenate with 3 or 4 joins

Question

Possible Duplicate:
combine multiple rows int one row with many to many

Using SQL Server I have the following tables/ data

CUS_VISIT

Cus_ID    Cus_Vis_ID
1           01
1           02
2           01

and

CUS_EVENT

Cus_Event_ID    Cus_ID    Cus_Vis_ID
001               1            01
002               1            01

and

CUS_XREF

Cus_ID    Cus_Event_ID    Cus_Prt_ID    Cus_Seq_No
1            001               1           1
1            001               2           1

and

CUS_PRT

Cus_ID    Cus_Prt_ID    Prt_Cd
1            1            1A
1            2            2A

I am trying to get the following

SQL RESULTS

Cus_ID     Prt_Cd     Cus_Vis_ID
1           1A,2A        1

what I end up with is

SQL RESULTS

Cus_ID     Prt_Cd     Cus_Vis_ID
1           1A           1
1           2A           1

The tables are linked by ...

CUS_VISIT.Cus_ID    = CUS_EVENT.Cus_ID 
CUS_VISIT.Cus_Vis_ID  = CUS_EVENT.Cus_Vis_ID
CUS_VISIT.Cus_ID = CUS_XREF.Cus_ID
CUS_EVENT.Cus_Event_ID = CUS_XREF.Cus_Event_ID    
CUS_XREF.Cus_Prt_ID = CUS_PRT.Cus_Prt_ID 
CUS_XREF.Cus_ID = CUS_PRT.Cus_ID

I can almost get what I am after if I drop the CUS_XREF.Cus_Prt_ID = CUS_PRT.Cus_Prt_ID join but then I get all of the part codes (Prt_Cd) for the customer not just the ones for that visit.

here is what i have

select distinct CUS_EVENT.cus_id, CUS_EVENT.cus_visit_id,
    (Select CUS_PRT.prt_cd + ',' AS [text()]
    From CUS_PRT, CUS_XREF
    where 
    CUS_EVENT.cus_id=XREF.cus_id
    and CUS_EVENT.cus_event_id = XREF.cus_event_id 
    and CUS_XREF.cus_id=CUS_PRT.cus_id 
    and CUS_XREF.cus_prt_id = CUS_PRT.cus_prt_id 
    and CUS_XREF.prt_seq_no ='1'
    order by CUS_PRT.prt_cd
    for XML PATH('')) [Codes]
from CUS_EVENT

i made a similar post recently but didn't get any specific help. i think i need another sub-query some where. thanks for looking at this.

Roman Pekar · Answer 1 · 2012-11-06T13:59:05.017

2

Well I agree that there's numerous questions about it. You just need to write for xml

select
    V.Cus_ID,
    V.Cus_Vis_ID,
    stuff(
      (
        select ', ' + TP.Prt_Cd
        from CUS_EVENT as TE
            inner join CUS_XREF as TX on TX.Cus_Event_ID = TE.Cus_Event_ID and TX.Cus_ID = TE.Cus_ID
            inner join CUS_PRT as TP on TP.Cus_Prt_ID = TX.Cus_Prt_ID and TP.Cus_ID = TE.Cus_ID
        where
            TE.Cus_Vis_ID = V.Cus_Vis_ID and
            TE.Cus_ID = V.Cus_ID
        for xml path(''), type
      ).value('.', 'nvarchar(max)')
      , 1, 2, '')
from CUS_VISIT as V

SQL FIDDLE

edited Nov 06 '12 at 13:59

answered Nov 06 '12 at 13:51

Roman Pekar

107,110
28
195
197

hm i did have for xml in the query in my post. This seems to bring back a list of prt_cds but not the correct ones – user1023993 Nov 06 '12 at 18:24
Check SQL fiddle example, it seems to return correct results – Roman Pekar Nov 07 '12 at 05:41

Thangamani Palanisamy · Answer 2 · 2012-11-07T05:49:52.887

0

Please try this... But i haven't excuted this query. I have implemented same scenerio. Just try and let me know

SELECT  
      CUS.Cus_ID
    , CUS.Cus_Vis_ID
    , Prt_Cd=STUFF(
                       (SELECT
                            ', ' + S.Prt_Cd
                            FROM CUS_PRT s
                            INNER JOIN CUS_XREF XREF ON  CUS.cus_id=XREF.cus_id AND s.cus_id=XREF.cus_id AND XREF.Cus_Prt_ID = s.Cus_Prt_ID
                            FOR XML PATH(''), TYPE
                       ).value('.','varchar(max)')
                       ,1,2, ''
                  )   

    FROM CUS_EVENT CUS 
    GROUP BY CUS.Cus_ID,CUS.Cus_Vis_ID

edited Nov 07 '12 at 05:49

answered Nov 06 '12 at 13:56

Thangamani Palanisamy

5,152
4
32
39

this is close but i am still getting all of the prt_cds for a customer instead of all of the prt_cd's for a customer and cus_event_id – user1023993 Nov 06 '12 at 17:40
@user1023993: Please see my post again. i have corrected the query and now its displaying the records as per requirement – Thangamani Palanisamy Nov 07 '12 at 05:51

concatenate with 3 or 4 joins

2 Answers2