Can a Dynamic SQL(where columns are created dynamically) be converted to NSQL and be used in Grid portlet..?

[ManjuN]

#Trying to create a new portlet , where an assignment label/field needs to be pulled into the portlet.

The availability of column(assignment label) is dynamic , then only if user enters teh value, teh column shall be available in DB, in such instance, can an NSQL be generated ..?

Able to create SQL, but unable to convert such dynamic NSQL.

[Deactivated User]

Please provide more details.

Using left joins it is possible to get data columns where there are no specific values.

Are you saying you have the SQL? If so please share it.

[ManjuN]

Hello,

 

The requirement is suppose there are category A, B and C in database so i want to display amount for category A, B and C column wise, if anyone adds new category D then my query automatically takes category D as 4th column in query output. I prepared the SQL where i have taken category values from database in a variable and passed that into PIVOT, but now i am unable to convert it into NSQL. 

 

Output should be displayed as 

A	B	C	D
1000	1000	1000	1000

 

SQL Code:-

 

DECLARE @cols AS VARCHAR(1000)='';
DECLARE @query AS VARCHAR(1000)='';
SELECT @cols = @cols + QUOTENAME(PRCATEGORY) + ',' FROM (select distinct PRCATEGORY from PRJ_RESOURCES ) as tmp
set @query = ,
N'SELECT * from
(
select ptt.PRPROJECTID,prj_resources.PRCATEGORY , PAA.ACTCOST_SUM
from Niku.PrTask PTT inner Join niku.prassignment PAA ON PAA.prTaskId=PTT.prid inner join niku.prj_resources on PAA.PRRESOURCEID = prj_resources.prid
inner join niku.srm_resources on PAA.PRRESOURCEID = srm_resources.id
where ptt.PRISTASK=1 and prj_resources.PRCATEGORY is NOT NULL and srm_resources.RESOURCE_TYPE in (0,1,2,3)
) src
pivot
(
SUM(PAA.ACTCOST_SUM) for PAA.PRCATEGORY in ( ' + @cols + ')
) piv'

execute(@query)

 

 

 

Any help would be much appreciated. 

[Roland Parrotte]

You could consider doing this as a JasperSoft report, instead of a Portlet.

[Albert Vincentpaul]

Hello ManjuN,

 

Try creating a 2 dimensional portlet. The columns on the second dimension are dynamic based on query output, as the Category gets added or removed, it will be reflected in the portlet.

 

You will need atleast one column on the first dimension. If you don't have any column for the first dimension, try using a fixed text - "Resource Cost by Category".