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)