Unofficial UNIT-e Forum

Code: Select all

CREATE FUNCTION [dbo].[TVF_AddressDetails] (
    @AddressID numeric(16, 0)
)
RETURNS TABLE
AS

RETURN
/*
	DECLARE @AddressID numeric(16, 0) = 727000001678952
*/

	SELECT
		AddressLine1 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 1 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine2 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 2 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine3 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 3 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine4 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 4 THEN ADS.Value 
					ELSE NULL 
				END
			),
		AddressLine5 = 
			MIN ( 
				CASE
					WHEN ADS.RowNumber = 5 THEN ADS.Value 
					ELSE NULL 
				END
			),
		PostCode = AD.a_reference
	FROM ulive.dbo.capd_address AD
	CROSS APPLY GC.dbo.TVF_SplitStrings ( AD.a_name, CHAR(10) ) ADS
	WHERE
		AD.a_id = @AddressID
	GROUP BY
		AD.a_reference