combine multiple rows int one row with many to many

Question

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 have tried numerous queries but didn't include them as i thought it would confuse things... most of them have used the xml path functionality but to no avail (i haven't been trying pivot since there is a massive list of codes).

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.

If it would help to see my unsuccessful queries or if I have left anything out sorry just let me know. Thanks for any help.

--

here is my subquery

stuff((
                  select ',' + B.prt_cd
                    from CUS_PRT B 
                    WHERE B.prt_cd = XREF.prt_cd  -- this is killing me
                    and XREF.cus_id=B.cus_id and 
                    -- some extra criteria i need but didn't think involved in my issue
                    B.prt_typ='1'  
                    and B.valid_entry='Y' and B.cur_entry_ind='Y'
                    for xml path('')),1,1,'')

here is my latest attempt. I added a new post because I didn't realize an edit would move it to the top of the list again... I think i need another subquery somewhere but not sure where and how to tie it to the other two queries.

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

score 1 · Answer 1 · edited May 23 '17 at 12:27

1

You need to do a subquery within your select in order to concatenate values from multiple rows into a single row.

See this answer for a solution: https://stackoverflow.com/a/545672/745511

edited May 23 '17 at 12:27

Community

1
1

answered Nov 01 '12 at 13:18

Nick Orlando

484
1
5
17

when i try that i get either multiple rows or all of the part codes. going to try adding query to main post – user1023993 Nov 01 '12 at 13:31
can anyone see why the solution above isn't working in the sub query added to the origonal post? – user1023993 Nov 01 '12 at 15:16

score 0 · Accepted Answer · answered Nov 23 '12 at 18:39

i resolved this issue by creating a view that just associates the prt_cd with the event and then another view based on this one that combines (concatenates) all of the prt_cd s for one event... not sure why but was never able to find a way to do is all in one shot with one query.

combine multiple rows int one row with many to many

2 Answers2

Linked

Related