Hi all,

I tried looking at these but was unable to get it to work as I wanted. In the end I set a maximum of 10 columns, and aliased the table 10 times then used a CTE for each column item.

Working result

WITH LinkedClient1(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA01%')

,

LinkedClient2(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA02%'),

LinkedClient3(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA03%'),

LinkedClient4(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA04%'),

LinkedClient5(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA05%')

,

LinkedClient6(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA06%')

,

LinkedClient7(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA07%'),

LinkedClient8(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA08%'),

LinkedClient9(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA09%'),

LinkedClient10(ClientRef, LinkedClientRef, LinkedClientName, LinkDesc, LinkACno, LinkType, LinkRel)

AS

(

SELECT

LinkedAccounts.ClientRef, Cl.ClientRef AS [LinkedClientRef], Cl.ReportName AS [LinkedClientName],

CASE WHEN LinkedAccounts.Description IS NULL THEN 'No description' ELSE LinkedAccounts.Description END,

CASE WHEN Cl.AccountNumber IS NULL THEN '' ELSE Cl.AccountNumber END,

CASE WHEN LinkedAccounts.AccountType IS NULL THEN 'Main' ELSE LinkType.TypeName END,

CASE WHEN LinkedAccounts.Relationship IS NULL THEN '' ELSE LinkedRel.TypeName END

FROM (((LinkedAccounts

LEFT JOIN Clients Cl ON LinkedAccounts.LinkedAccountRef = Cl.ClientRef)

LEFT JOIN TypeNames LinkedRel ON (LinkedAccounts.Relationship = LinkedRel.Ref1 AND LinkedRel.TypeNameSet = 115))

LEFT JOIN TypeNames LinkType ON (LinkedAccounts.AccountType = LinkType.Ref1 AND LinkType.TypeNameSet = 114))

WHERE LinkedAccounts.Description LIKE 'LA10%')

SELECT

SubQuery.ClientRef,

SubQuery.Account_Number AS [Account Number],

SubQuery.ClientName AS [Client Name],

SubQuery.[Linked Client 1],

SubQuery.[Linked Client 1 A/C No],

SubQuery.[Linked Client 2],

SubQuery.[Linked Client 2 A/C No],

SubQuery.[Linked Client 3],

SubQuery.[Linked Client 3 A/C No],

SubQuery.[Linked Client 4],

SubQuery.[Linked Client 4 A/C No],

SubQuery.[Linked Client 5],

SubQuery.[Linked Client 5 A/C No],

SubQuery.[Linked Client 6],

SubQuery.[Linked Client 6 A/C No],

SubQuery.[Linked Client 7],

SubQuery.[Linked Client 7 A/C No],

SubQuery.[Linked Client 8],

SubQuery.[Linked Client 8 A/C No],

SubQuery.[Linked Client 9],

SubQuery.[Linked Client 9 A/C No],

SubQuery.[Linked Client 10],

SubQuery.[Linked Client 10 A/C No]

FROM

(

SELECT

Clients.ClientRef,

Clients.AccountNumber AS [Account_Number],

Clients.ReportName AS [ClientName],

LinkedClient1.LinkDesc+' - '+LinkedClient1.LinkedClientName+' - REL:'+LinkedClient1.LinkRel+' - Type: '+LinkedClient1.LinkType

AS [Linked Client 1],

LinkedClient1.LinkACNo AS [Linked Client 1 A/C No],

LinkedClient2.LinkDesc+' - '+LinkedClient2.LinkedClientName+' - REL:'+LinkedClient2.LinkRel+' - Type: '+LinkedClient2.LinkType

AS [Linked Client 2],

LinkedClient2.LinkACNo AS [Linked Client 2 A/C No],

LinkedClient3.LinkDesc+' - '+LinkedClient3.LinkedClientName+' - REL:'+LinkedClient3.LinkRel+' - Type: '+LinkedClient3.LinkType

AS [Linked Client 3],

LinkedClient3.LinkACNo AS [Linked Client 3 A/C No],

LinkedClient4.LinkDesc+' - '+LinkedClient4.LinkedClientName+' - REL:'+LinkedClient4.LinkRel+' - Type: '+LinkedClient4.LinkType

AS [Linked Client 4],

LinkedClient4.LinkACNo AS [Linked Client 4 A/C No],

LinkedClient5.LinkDesc+' - '+LinkedClient5.LinkedClientName+' - REL:'+LinkedClient5.LinkRel+' - Type: '+LinkedClient5.LinkType

AS [Linked Client 5],

LinkedClient5.LinkACNo AS [Linked Client 5 A/C No],

LinkedClient6.LinkDesc+' - '+LinkedClient6.LinkedClientName+' - REL:'+LinkedClient6.LinkRel+' - Type: '+LinkedClient6.LinkType

AS [Linked Client 6],

LinkedClient6.LinkACNo AS [Linked Client 6 A/C No],

LinkedClient7.LinkDesc+' - '+LinkedClient7.LinkedClientName+' - REL:'+LinkedClient7.LinkRel+' - Type: '+LinkedClient7.LinkType

AS [Linked Client 7],

LinkedClient7.LinkACNo AS [Linked Client 7 A/C No],

LinkedClient8.LinkDesc+' - '+LinkedClient8.LinkedClientName+' - REL:'+LinkedClient8.LinkRel+' - Type: '+LinkedClient8.LinkType

AS [Linked Client 8],

LinkedClient8.LinkACNo AS [Linked Client 8 A/C No],

LinkedClient9.LinkDesc+' - '+LinkedClient9.LinkedClientName+' - REL:'+LinkedClient9.LinkRel+' - Type: '+LinkedClient9.LinkType

AS [Linked Client 9],

LinkedClient9.LinkACNo AS [Linked Client 9 A/C No],

LinkedClient10.LinkDesc+' - '+LinkedClient10.LinkedClientName+' - REL:'+LinkedClient10.LinkRel+' - Type: '+LinkedClient10.LinkType

AS [Linked Client 10],

LinkedClient10.LinkACNo AS [Linked Client 10 A/C No]

FROM ((((((((((Clients

LEFT JOIN LinkedClient1 ON Clients.ClientRef = LinkedClient1.ClientRef)

LEFT JOIN LinkedClient2 ON Clients.ClientRef = LinkedClient2.ClientRef)

LEFT JOIN LinkedClient3 ON Clients.ClientRef = LinkedClient3.ClientRef)

LEFT JOIN LinkedClient4 ON Clients.ClientRef = LinkedClient4.ClientRef)

LEFT JOIN LinkedClient5 ON Clients.ClientRef = LinkedClient5.ClientRef)

LEFT JOIN LinkedClient6 ON Clients.ClientRef = LinkedClient6.ClientRef)

LEFT JOIN LinkedClient7 ON Clients.ClientRef = LinkedClient7.ClientRef)

LEFT JOIN LinkedClient8 ON Clients.ClientRef = LinkedClient8.ClientRef)

LEFT JOIN LinkedClient9 ON Clients.ClientRef = LinkedClient9.ClientRef)

LEFT JOIN LinkedClient10 ON Clients.ClientRef = LinkedClient10.ClientRef)

) AS [SubQuery]

GROUP BY SubQuery.ClientRef, SubQuery.Account_Number, SubQuery.ClientName

, SubQuery.[Linked Client 1], SubQuery.[Linked Client 1 A/C No]

, SubQuery.[Linked Client 2], SubQuery.[Linked Client 2 A/C No]

, SubQuery.[Linked Client 3], SubQuery.[Linked Client 3 A/C No]

, SubQuery.[Linked Client 4], SubQuery.[Linked Client 4 A/C No]

, SubQuery.[Linked Client 5], SubQuery.[Linked Client 5 A/C No]

, SubQuery.[Linked Client 6], SubQuery.[Linked Client 6 A/C No]

, SubQuery.[Linked Client 7], SubQuery.[Linked Client 7 A/C No]

, SubQuery.[Linked Client 8], SubQuery.[Linked Client 8 A/C No]

, SubQuery.[Linked Client 9], SubQuery.[Linked Client 9 A/C No]

, SubQuery.[Linked Client 10], SubQuery.[Linked Client 10 A/C No]

ORDER BY SubQuery.ClientName