Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- USE [ArenaDB]
- GO
- /****** Object: StoredProcedure [dbo].[cust_lpc_evnt_registrant_fieldviewer] Script Date: 07/31/2014 08:27:25 ******/
- SET ANSI_NULLS ON
- GO
- SET QUOTED_IDENTIFIER ON
- GO
- ALTER PROC [dbo].[cust_lpc_evnt_registrant_fieldviewer]
- @profile INT,
- @showfields varchar(max) = '',
- @OrganizationID INT=1
- AS
- --DECLARE @profile INT =12452
- --DECLARE @showfields varchar(max)=''
- DECLARE @fields VARCHAR(MAX)
- DECLARE @sql NVARCHAR(MAX), @parameters NVARCHAR(MAX)
- SELECT @fields=COALESCE(@FIELDS+',','')+'['+x.title+']'
- FROM
- (SELECT DISTINCT ccf.title
- FROM
- core_profile_member_field AS cpmf
- JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
- JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
- LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cpmf.custom_field_id AND cpmfv.profile_id=cpmf.profile_id AND cpmfv.person_id=cpm.person_id
- JOIN core_person AS cp ON cp.person_id=cpm.person_id
- JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
- WHERE cpro.profile_id=@profile
- UNION ALL
- SELECT DISTINCT ccf.title
- FROM
- core_profile_member_field_module AS cpmfm
- JOIN core_profile AS cpro ON cpro.profile_id=cpMfm.profile_id
- JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
- JOIN core_custom_field_module_field cfmf ON cpmfm.custom_field_module_id = cfmf.custom_field_module_id
- LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cfmf.custom_field_id AND cpmfv.profile_id=cpmfm.profile_id AND cpmfv.person_id=cpm.person_id
- JOIN core_person AS cp ON cp.person_id=cpm.person_id
- JOIN core_custom_field AS ccf ON ccf.custom_field_id=cfmf.custom_field_id
- WHERE cpro.profile_id=@profile
- ) AS x
- SET @sql = 'SELECT *
- FROM
- (
- SELECT cp.person_id, cp.last_name+'', ''+cp.first_name AS [person_name], ccf.title, REPLACE(ISNULL(cpmfv.selected_value,''''),''^'','' '') AS selected_value
- FROM core_profile_member_field AS cpmf
- JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
- JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
- LEFT OUTER JOIN core_profile_member_field_value AS cpmfv ON cpmfv.custom_field_id=cpmf.custom_field_id AND cpmfv.profile_id=cpmf.profile_id AND cpmfv.person_id=cpm.person_id
- JOIN core_person AS cp ON cp.person_id=cpm.person_id
- JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
- WHERE cpro.profile_id = @profileID
- ) AS s
- PIVOT
- (
- MAX(selected_value)
- FOR [title] IN ('+@fields+')
- ) AS p'
- SELECT @sql
- SET @parameters = '@profileID INT'
- EXEC sp_executesql @sql=@sql, @params=@parameters, @profileID=@profile
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement