I had a need for getting a property details for a SSR report and came up with the following store procedure  . . . I thought it might be useful to someone.

/****** Object: StoredProcedure [dbo].[GIBS_Rentals_Get_PropertyDetails] Script Date: 12/15/2014 06:52:03 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[GIBS_Rentals_Get_PropertyDetails]

(

@PropertyID int

)

AS

BEGIN

SET NOCOUNT ON;

DECLARE @cols AS VARCHAR(MAX),

@query AS VARCHAR(MAX),

@PropertyIDVarchar AS VARCHAR(10)

Set @PropertyIDVarchar = Convert(VARCHAR(10),@PropertyID)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(name)

FROM Ventrian_PropertyAgent_CustomField

FOR XML PATH(''), TYPE

).value('.', 'VARCHAR(MAX)')

,1,1,'')

--print @cols

set @query

= 'SELECT PropertyID,' + @cols + ' from

(

SELECT

PropertyID ,

cast(Ventrian_PropertyAgent_PropertyValue.CustomValue as Varchar(50)) AS CustomValue,

Ventrian_PropertyAgent_CustomField.Name AS [Name]

FROM Ventrian_PropertyAgent_PropertyValue

INNER JOIN

Ventrian_PropertyAgent_CustomField ON Ventrian_PropertyAgent_PropertyValue.CustomFieldID = Ventrian_PropertyAgent_CustomField.CustomFieldID

WHERE Ventrian_PropertyAgent_PropertyValue.PropertyID = ' + @PropertyIDVarchar + '

) x

pivot

(

min(CustomValue)

for Name in (' + @cols + ')

) p '

--print @query

execute(@query)

END