-- Create tables
DECLARE @Entity TABLE (
	[ID] INT
	, [LastName] NVARCHAR(255)
	, [FirstName] NVARCHAR(255)
	, [ParentEntityID] INT
	)

DECLARE @EntityAddress TABLE (
	[Id] INT
	, [entityId] INT
	, [address] NVARCHAR(512)
	)

DECLARE @StatementHeader TABLE (
	[id] INT
	, [entityID] INT
	, [amountDue] MONEY
	)

-- Populate Data
INSERT @entity ([Id], [lastname], [firstname], [parentEntityId])
      SELECT 1, 'Jones', 'Jeff', 0
UNION SELECT 2, 'Stevens', 'Mark', 0
UNION SELECT 3, 'Jones', 'Cindy', 1
UNION SELECT 4, 'Stevens', 'Debbie', 2

INSERT @EntityAddress ([Id], [entityId], [address])
      SELECT 1, 1, 'jeffjones@yahoo.com'
UNION SELECT 2, 1, 'jjones@businessname.com'
UNION SELECT 3, 3, 'cindyjones@yahoo.com'
UNION SELECT 4, 2, 'markjones@gmail.com'

INSERT @StatementHeader ([Id], [entityID], [amountDue])
      SELECT 1, 1, 25.00
UNION SELECT 2, 2, 50.00

-- Select
SELECT
	sh.[Id] AS [Statement Id]
	, sh.[amountDue] AS [Amount Due]
	, e.[firstname] AS [Entity Firstname]
	, e.[lastname] AS [Entity Lastname]
	, ea.[address] AS [Entity Address]
	, ep.[firstname] AS [Parent Firstname]
	, ep.[lastname] AS [Parent Lastname]
	, eap.[address] AS [Parent Address]
FROM
	@statementHeader sh
	JOIN @entity e ON e.[ID] = sh.[entityId]
	JOIN @entityAddress ea ON ea.[entityId] = e.[ID]

	LEFT JOIN @entity ep ON ep.[parentEntityID] = e.[iD]
	LEFT JOIN @entityAddress eap ON eap.[entityID] = ep.[Id]

-- Create tables
DECLARE @Entity TABLE (
	[ID] INT
	, [LastName] NVARCHAR(255)
	, [FirstName] NVARCHAR(255)
	, [ParentEntityID] INT
	)

DECLARE @EntityAddress TABLE (
	[Id] INT
	, [entityId] INT
	, [address] NVARCHAR(512)
	)

DECLARE @StatementHeader TABLE (
	[id] INT
	, [entityID] INT
	, [amountDue] MONEY
	)

-- Table to hold the combined addresses for entities
DECLARE @combinedEnts TABLE (
	[entityId] INT
	, [parentEntityId] INT
	, [lastname] NVARCHAR(MAX)
	, [firstname] NVARCHAR(MAX)
	, [address] NVARCHAR(MAX)
	)

-- Populate Data
-- Fred flintstone is a new Parent of Cindy...
-- Therefore his a grandfather of Jeff

INSERT @entity ([Id], [lastname], [firstname], [parentEntityId])
      SELECT 1, 'Jones', 'Jeff', 0
UNION SELECT 2, 'Stevens', 'Mark', 0
UNION SELECT 3, 'Jones', 'Cindy', 1
UNION SELECT 4, 'Stevens', 'Debbie', 2
UNION SELECT 5, 'Flintstone', 'Fred', 3

INSERT @EntityAddress ([Id], [entityId], [address])
      SELECT 1, 1, 'jeffjones@yahoo.com'
UNION SELECT 2, 1, 'jjones@businessname.com'
UNION SELECT 3, 3, 'cindyjones@yahoo.com'
UNION SELECT 4, 2, 'markjones@gmail.com'
UNION SELECT 5, 5, 'fred@bedrock.com'

INSERT @StatementHeader ([Id], [entityID], [amountDue])
      SELECT 1, 1, 25.00
UNION SELECT 2, 2, 50.00

-- Make up a list of combined addresses per entity
INSERT @combinedEnts ([entityID], [parentEntityId], [lastname], [firstname], [address])
SELECT DISTINCT
	e.[ID]
	, [parentEntityId]
	, [lastname]
	, [firstname]
	, LEFT(d.[address], LEN(d.[address]) - 1) AS [address]
FROM
	@entity e
	JOIN @EntityAddress ea ON ea.[entityId] = e.[Id]

	CROSS APPLY (
		SELECT
			ea.[address] + '; '
		FROM
			@entity e2
			JOIN @EntityAddress ea ON ea.[entityId] = e.[ID]
		WHERE
			e.[ID] = e2.[Id]
		ORDER BY
			ea.[address]
		FOR XML PATH('')
	)
	D ( [address])


-- SELECT statement
;WITH addresses (
	[entities]
	, [entityID]
	, [rootEntityId]
	, [names]
	, [address]
	, [level]
	)
AS
	(
	SELECT
		CAST(e.[entityId] AS NVARCHAR(MAX))
		, e.[entityId]
		, e.[entityId]
		, CAST((e.[firstname] + ' ' + e.[lastname]) AS NVARCHAR(MAX))
		, CAST(e.[address] AS NVARCHAR(MAX))
		, 0
	FROM
		@combinedEnts e
	UNION ALL SELECT
		a.[entities] + ', ' + CAST(e.[entityId] AS NVARCHAR(MAX))
		, e.[entityId]
		, CAST(LEFT(a.[entities], CHARINDEX(a.[entities], ',') + 1) AS INT)
		, a.[names] + ', ' + CAST((e.[firstname] + ' ' + e.[lastname]) AS NVARCHAR(MAX))
		, a.[address] + '; ' + CAST(e.[address] AS NVARCHAR(MAX))
		, [level] + 1
	FROM
		addresses a
		JOIN @combinedEnts e ON e.[parentEntityId] = a.[entityID]
	WHERE
		', ' + a.[entities] + ', ' NOT LIKE '%, ' + CAST(e.[entityID] AS NVARCHAR(MAX)) + ', %'
	)
SELECT
	sh.[Id] AS [Statement Header Id]
	, sh.[amountDue] AS [Amount Due]
	, a.[names] AS [Names]
	, a.[address] AS [addresses]
FROM
	@statementHeader sh
	JOIN (
	SELECT
		[rootEntityId] AS [reId]
		, [names] AS [names]
		, [address] AS [address]
		, ROW_NUMBER() OVER(PARTITION BY [rootEntityId] ORDER BY [level] DESC) AS [pos]
	FROM
		addresses
	)
	a ON
		a.[reID] = sh.[entityId]
		AND a.[pos] = 1

quote:

---

Originally posted by Transact Charlie

Actually -- this is a *lot* harder than it first appears!

This better not be an exam question or similar! -- if it is then the person who set it either doesn't know how hard it is or is a complete bastard!

Assuming that there can be any number of parents and that all parents need to be emailed per order (and only 1 line per order)........

---