Has any one created dynamic columns in the cCJGRID? I have built a Pivot table in MS SQL that I want to be able to display. The number of columns will be based on the number of Income type / Territory combinations. Will always be one but could be as many as use has created.
I included the MS SQL Procedure
Thanks for the help
USE [SRSQL]
GO
DROP PROCEDURE [dbo].[CLNTPAY_PIVOT]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE Procedure [dbo].[CLNTPAY_PIVOT] (@SONGID nvarchar(12)) as
DECLARE @query VARCHAR(4000)
DECLARE @Income varchar (2000)
SELECT @Income = STUFF(( SELECT DISTINCT
'],[' + ltrim(INCOME_TYPE) + '/' + ltrim(TERRITORY)
FROM CLNTPAY
WHERE INCOME_TYPE > '' AND INCOME_TYPE is not Null and CLNTPAY.SONG#=@SONGID
ORDER BY '],[' + ltrim(INCOME_TYPE) + '/' + ltrim(TERRITORY)
FOR XML PATH('')
), 1, 2, '') + ']'
SET @query =
'SELECT * FROM(
SELECT
CLNTPAY.[SONG#]
,SONG.TITLE
,CLNTPAY.[PAYOR#]
,PUBLISHR.NAME [PAYOR_NAME]
,CLNTPAY.[CLIENT#]
,CLIENT.NAME [CLIENT_NAME]
,CLIENT.[TYPE]
,CLNTPAY.[RATE_TYPE]
,CLNTPAY.[AT_SOURCE]
,CLNTPAY.[EXCEPTIONS]
,CLNTPAY.[MANUAL_PROCESS]
,CLNTPAY.[ADMIN_FEE]
,(ltrim(CLNTPAY.INCOME_TYPE) + ''/'' + ltrim(CLNTPAY.TERRITORY)) INCOME_TYPE
,SUM(RATE) [RATE]
FROM [dbo].[CLNTPAY]
LEFT JOIN SONG on CLNTPAY.SONG#=SONG.SONG#
LEFT JOIN PUBLISHR on CLNTPAY.PAYOR#=PUBLISHR.PUBLISHER#
LEFT JOIN CLIENT on CLNTPAY.CLIENT#=CLIENT.CLIENT#
WHERE CLNTPAY.SONG#=''' + @SONGID + '''
Group by CLNTPAY.[SONG#]
,SONG.TITLE
,CLNTPAY.[PAYOR#]
,PUBLISHR.NAME
,CLNTPAY.[CLIENT#]
,CLIENT.NAME
,CLIENT.[TYPE]
,CLNTPAY.[RATE_TYPE]
,CLNTPAY.[AT_SOURCE]
,CLNTPAY.[EXCEPTIONS]
,CLNTPAY.[MANUAL_PROCESS]
,CLNTPAY.[ADMIN_FEE]
,CLNTPAY.INCOME_TYPE
,CLNTPAY.TERRITORY
) R
PIVOT (SUM([RATE]) FOR INCOME_TYPE IN ('+@Income+')) AS pvt'
EXECUTE (@query)