/*
Table: Person
| PersonId | Name |
| 1 | John |
| 2 | Lisa |
| 3 | Jack |

Table: Quote
| PersonId | Quote |
| 1 | "Not sure how I’m.." |
| 1 | "I’ve done this before." |
| 2 | "Don’t you dare!" |
| 2 | "It’s easy." |
| 2 | "Ggrrr.." |
| 3 | "It wasn’t me!" |

OK, so what I want to do is to create a query that will join the two tables and concat all the quotes from each person into a single column, something like this:

| PersonId | Name | ConcatQuotes |
| 1 | John | "Not sure how I’m.." - "I’ve done this before." |
| 2 | Lisa | "Don’t you dare!" - "It’s easy" - "Ggrrr.." |
| 3 | Jack | "It wasn’t me!" |
*/

DECLARE @person TABLE ([personID] INT, [name] CHAR(4))
INSERT @person ([personId], [name])
      SELECT 1, 'John'
UNION SELECT 2, 'Lisa'
UNION SELECT 3, 'Jack'

DECLARE @quote TABLE ([personId] INT, [quote] VARCHAR(255))
INSERT @quote ([personId], [quote])
      SELECT 1, '"Not sure how I''m.."'
UNION SELECT 1, '"I''ve done this before."'
UNION SELECT 2, '"Don''t you dare!"'
UNION SELECT 2, '"It''s easy."'
UNION SELECT 2, '"Grrrr.."'
UNION SELECT 3, '"It wasn''t me!"'

SELECT * FROM @quote

SELECT
	[personID]
	, [name]
	, LEFT([quotes], LEN([quotes]) -2) AS [ConcatQuotes]
FROM
	(
	SELECT
		p.[personID]
		, p.[name]
		, (SELECT [quote] + ' - ' FROM @quote q WHERE q.[personID] = p.[personID] ORDER BY [quote] FOR XML PATH('')) AS [quotes]
	FROM
		@person p
	)
	q