1,633 questions

3,178 answers

1,706 comments

478 users

Sharing some code: Comma delineated list of constituencies using CROSS APPLY.

0 votes

I wasn't sure where to post this so...
I wanted to share some code I put together to return a comma delineated list of constituencies for a constituent using CROSS APPLY.

Just in case anyone out there ever needs to do this.

WITH ConstituentInfo
AS
(
SELECT
 CON.ID         AS CONSTITUENTID
,CON.LOOKUPID   AS LOOKUPID
,CON.FIRSTNAME  AS FIRSTNAME
,CON.MIDDLENAME  AS MIDDLENAME
,CON.KEYNAME        AS LASTNAME
,CON.MAIDENNAME AS MAIDENNAME
,CON.GENDER          AS GENDER
,CONVERT(NVARCHAR(500),STUFF(ConstituencyList,LEN(ConstituencyList),1,'')) AS CONSTITUENCYLIST
FROM dbo.CONSTITUENT AS CON WITH(READUNCOMMITTED)
-- Remove dead people:
LEFT JOIN dbo.DECEASEDCONSTITUENT AS DCON WITH(READUNCOMMITTED)
ON DCON.ID = CON.ID
/*
The CROSS APPLY operator:
Here's a good site:
https://www.simple-talk.com/sql/t-sql-programming/sql-server-apply-basics/

Below I am creating a comma delimited string.
All rows returned by the table valued function will get concatenated together.

Examples:
https://www.google.com/?gws_rd=ssl#q=sql+server+cross+apply+for+xml+path
*/
CROSS APPLY
(
      SELECT
                 C.[DESCRIPTION] + ','
         FROM dbo.UFN_CONSTITUENT_GETALLCONSTITUENCIES(CON.ID) AS C
         ORDER BY C.CONSTITUENCYTYPECODE DESC
         FOR XML PATH('')
) AS cList (ConstituencyList)

WHERE CON.ISGROUP = 0
      AND CON.ISORGANIZATION = 0
      AND DCON.ID IS NULL
)
SELECT
 CI.CONSTITUENTID
,CI.LOOKUPID
,CI.FIRSTNAME
,CI.MIDDLENAME
,CI.LASTNAME
,CI.MAIDENNAME
,CI.GENDER
,CI.CONSTITUENCYLIST
FROM ConstituentInfo AS CI

If you run this for all constituents it will take some time to return but, if you run this for a specific constituent it's pretty quick. There are other ways to do this but, I wanted to try using CROSS APPLY.

Will

asked Feb 23 by willomalley (195 points)   2 8 24

Please log in or register to answer this question.

...