posted on December 15, 2014 08:22
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