Advertisement
Guest User

Untitled

a guest
Jul 31st, 2014
164
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 2.60 KB | None | 0 0
  1. USE [ArenaDB]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[cust_lpc_evnt_registrant_fieldviewer]    Script Date: 07/31/2014 08:27:25 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROC [dbo].[cust_lpc_evnt_registrant_fieldviewer]
  9. @profile INT,
  10. @showfields varchar(max) = '',
  11. @OrganizationID INT=1
  12.  
  13. AS
  14.  
  15. --DECLARE @profile INT =12452
  16. --DECLARE @showfields varchar(max)=''
  17.  
  18. DECLARE @fields VARCHAR(MAX)
  19. DECLARE @sql NVARCHAR(MAX), @parameters NVARCHAR(MAX)
  20.  
  21.  
  22. SELECT @fields=COALESCE(@FIELDS+',','')+'['+x.title+']'
  23. FROM
  24. (SELECT DISTINCT ccf.title
  25. FROM
  26. core_profile_member_field AS cpmf
  27. JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
  28. JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
  29. 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
  30. JOIN core_person AS cp ON cp.person_id=cpm.person_id
  31. JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
  32. WHERE cpro.profile_id=@profile
  33. UNION ALL
  34. SELECT DISTINCT ccf.title
  35. FROM
  36. core_profile_member_field_module AS cpmfm
  37. JOIN core_profile AS cpro ON cpro.profile_id=cpMfm.profile_id
  38. JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
  39. JOIN core_custom_field_module_field cfmf ON cpmfm.custom_field_module_id = cfmf.custom_field_module_id
  40. 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
  41. JOIN core_person AS cp ON cp.person_id=cpm.person_id
  42. JOIN core_custom_field AS ccf ON ccf.custom_field_id=cfmf.custom_field_id
  43. WHERE cpro.profile_id=@profile
  44. ) AS x
  45.  
  46. SET @sql = 'SELECT *
  47. FROM
  48. (
  49. SELECT cp.person_id, cp.last_name+'', ''+cp.first_name AS [person_name], ccf.title, REPLACE(ISNULL(cpmfv.selected_value,''''),''^'','' '') AS selected_value
  50. FROM core_profile_member_field AS cpmf
  51. JOIN core_profile AS cpro ON cpro.profile_id=cpMf.profile_id
  52. JOIN core_profile_member AS cpm ON cpm.profile_id=cpro.profile_id
  53. 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
  54. JOIN core_person AS cp ON cp.person_id=cpm.person_id
  55. JOIN core_custom_field AS ccf ON ccf.custom_field_id=cpmf.custom_field_id
  56. WHERE cpro.profile_id = @profileID
  57. ) AS s
  58. PIVOT
  59. (
  60. MAX(selected_value)
  61. FOR [title] IN ('+@fields+')
  62. ) AS p'
  63.  
  64. SELECT @sql
  65.  
  66. SET @parameters = '@profileID INT'
  67.  
  68. EXEC sp_executesql @sql=@sql, @params=@parameters, @profileID=@profile
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement