asked on

SQL - Concatenate data from multiple rows from two joined tables (FOR XML?)

Hi all,
Have two tables. Receipts and locations as below.
Can't get the for XML path to work right.

Want to pull a query such that the locations are concatenated:
RouteriD Location
10764 A5, A6, A16
10765 A5, A6, A16
10776 OrderReceipt
.....

Tables:

CREATE TABLE [dbo].[TReceipts](
	[ReceiptID] [int] IDENTITY(1,1) NOT NULL,
	[RouterID] [int] NULL,
	[LocID] [int] NULL,
	[Receiptdate] [date] NULL,
 CONSTRAINT [PK_TReceipts] PRIMARY KEY CLUSTERED 
(
	[ReceiptID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[TLocations](
	[LocID] [int] IDENTITY(1,1) NOT NULL,
	[DisplayBoardID] [int] NULL,
	[Location] [nvarchar](100) NULL,
 CONSTRAINT [PK_TLocations] PRIMARY KEY CLUSTERED 
(
	[LocID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

Data:
Treceipts:
ReceiptID      RouterID      LocID      Receiptdate
3359      10776      50      2017-09-18
3360      10777      50      2017-09-18
3361      10764      6      2017-09-19
3362      10765      6      2017-09-19
3363      10778      6      2017-09-19
3364      10779      6      2017-09-19
3365      10780      6      2017-09-19
3366      10781      6      2017-09-19
3367      10782      6      2017-09-19
3368      10783      6      2017-09-19
3369      10784      6      2017-09-19
3370      10785      6      2017-09-19
3371      10786      6      2017-09-19
3372      10787      6      2017-09-19
3373      10788      6      2017-09-19
3374      10764      5      2017-09-19
3375      10765      5      2017-09-19
3376      10778      5      2017-09-19
3377      10779      5      2017-09-19
3378      10780      5      2017-09-19
3379      10781      5      2017-09-19
3380      10782      5      2017-09-19
3381      10783      5      2017-09-19
3382      10784      5      2017-09-19
3383      10785      5      2017-09-19
3384      10786      5      2017-09-19
3385      10787      5      2017-09-19
3386      10788      5      2017-09-19
3387      10764      16      2017-09-19
3388      10765      16      2017-09-19
3389      10778      16      2017-09-19
3390      10779      16      2017-09-19
3391      10780      16      2017-09-19
3392      10781      16      2017-09-19
3393      10782      16      2017-09-19
3394      10783      16      2017-09-19
3395      10784      16      2017-09-19
3396      10785      16      2017-09-19
3397      10786      16      2017-09-19
3398      10787      16      2017-09-19
3399      10788      16      2017-09-19

Tlocations:
LocID      DisplayBoardID      Location
1      6      A1
2      6      A2
3      6      A3
4      6      A4
5      6      A5
6      6      A6
7      6      A7
8      6      A8
9      6      A9
10      6      A10
11      6      A11
12      6      A12
13      6      A13
14      6      A14
15      6      A15
16      6      A16
17      6      B1
18      6      B2
19      6      B3
20      6      B4
21      6      B5
22      6      B6
23      6      B7
24      6      B8
25      6      B9
26      6      B10
27      6      B11
28      6      B12
29      6      B13
30      6      B14
31      6      B15
32      6      B16
33      6      C1
34      6      C2
35      6      C3
36      6      C4
37      6      C5
38      6      C6
39      6      C7
40      6      C8
41      6      C9
42      6      C10
43      6      C11
44      6      C12
45      6      C13
46      6      C14
47      6      C15
48      6      C16
49      19      Metal Prep
50      4      OrderReceipt
51      6      Swim

Thanks In advance.

Pawan Kumar

Pls try -

SELECT b.[ReceiptID] 
			, STUFF 
				((
					SELECT ', ' + CAST(LocID AS VARCHAR(MAX)) 
					FROM [TReceipts] a
					WHERE ( a.[ReceiptID] = b.[ReceiptID] )
					FOR XML PATH('')
				) ,1,1,'') 
				AS cusr
FROM [TReceipts] b
GROUP BY b.[ReceiptID]

Open in new window

Output with sample

/*------------------------

SELECT b.[ReceiptID] 
			, STUFF 
				((
					SELECT ', ' + CAST(LocID AS VARCHAR(MAX)) 
					FROM [TReceipts] a
					WHERE ( a.[ReceiptID] = b.[ReceiptID] )
					FOR XML PATH('')
				) ,1,1,'') 
				AS cusr
FROM [TReceipts] b
GROUP BY b.[ReceiptID]
------------------------*/
ReceiptID   cusr
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
3359         50, 50
3360         50, 50
3361         6, 6
3362         6, 6
3363         6, 6
3364         6, 6
3365         6, 6
3366         6, 6
3367         6, 6
3374         5
3375         5
3376         5
3377         5
3378         5

(14 row(s) affected)

Open in new window

Pawan Kumar

Updated for RouterID

SELECT b.[RouterID] 
			, STUFF 
				((
					SELECT DISTINCT ', ' +  CAST(LocID AS VARCHAR(MAX)) 
					FROM [TReceipts] a
					WHERE ( a.[RouterID] = b.[RouterID] )
					FOR XML PATH('')
				) ,1,1,'') 
				AS cusr
FROM [TReceipts] b
GROUP BY b.[RouterID]

Open in new window

Output

/*------------------------
SELECT b.[RouterID] 
			, STUFF 
				((
					SELECT DISTINCT ', ' +  CAST(LocID AS VARCHAR(MAX)) 
					FROM [TReceipts] a
					WHERE ( a.[RouterID] = b.[RouterID] )
					FOR XML PATH('')
				) ,1,1,'') 
				AS cusr
FROM [TReceipts] b
GROUP BY b.[RouterID]
------------------------*/
RouterID    cusr
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
10764        5, 6
10765        5, 6
10776        50
10777        50
10778        5, 6
10779        5, 6
10780        5, 6
10781        6
10782        6

(9 row(s) affected)

Open in new window

kerrymr

ASKER

Very close just needs modified to get the letter on there as well?

Pawan Kumar

Which letter ? didnt get u

kerrymr

ASKER

I dont want the location ID, I want the location word.
This is what i need...
RouteriD Location
10764 A5, A6, A16
10765 A5, A6, A16
10776 OrderReceipt

ASKER CERTIFIED SOLUTION

Pawan Kumar

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

kerrymr

ASKER

Thanks!

kerrymr

ASKER

Thank you.