Dynamic Gridview with Pivot Table

Question

I am currently creating a gridview that has dynamic columns.

I have successfully created this from querying the log hours for every task per date on a specific sprint. The logged dates becomes the column and the task and logged hours become rows. With both vertical and horizontal totals. On this gridview, you can manually edit the logged hours and it will be saved to the database.

The problem now is the design changes. What I need to do is just list the sprint duration dates for columns and task on the first column even if there are still no logged hour on that task or date.

Any help will be appreciated. Thanks.

here is the stored procedure for this pivoted table

USE [JiraAutomation]
GO
/****** Object:  StoredProcedure [dbo].[logs]    Script Date: 4/12/2016 7:00:09 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[logs]
@username nvarchar(30),
@sprintId nvarchar(30)

AS
/* COLUMNS HEADERS */
Declare 
@cols as NVARCHAR(MAX)


select @cols =  STUFF((SELECT ',' + QUOTENAME(log_date)
            from tbl_log join tbl_task on tbl_task.task_id = tbl_log.task_id
              where tbl_log.username = @username
              and tbl_log.sprint_id = @sprintId
            group by log_date
            order by log_date
            FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'')

DECLARE @horiz_total nvarchar(MAX)

SELECT @horiz_total = stuff((
           SELECT '+isnull(' + quotename(log_date) + ',0)' 
           FROM tbl_log
           join tbl_task on tbl_task.task_id = tbl_log.task_id
              where tbl_log.username = @username
              and tbl_log.sprint_id = @sprintId 
           GROUP BY log_date 
           ORDER BY log_date 
        FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

DECLARE @vert_total nvarchar(MAX)

SELECT @vert_total = stuff((
           SELECT ',sum(' + quotename(log_date) + ')' 
           FROM tbl_log
           join tbl_task on tbl_task.task_id = tbl_log.task_id
              where tbl_log.username = @username
              and tbl_log.sprint_id = @sprintId 
           GROUP BY log_date 
           ORDER BY log_date 
          FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

DECLARE @isnulls nvarchar(MAX)

SELECT @isnulls = stuff((
           SELECT ',isnull(' + quotename(log_date) + ',0) as '+quotename(log_date) 
           FROM tbl_log 
           GROUP BY log_date 
           ORDER BY log_date 
           FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

DECLARE @query nvarchar(MAX)

SET @query = 'select task_description as TASK,' + @cols + ',' + @horiz_total + ' as Total 
              into #tmp_result
              from (select task_description, log_date, log_hours from tbl_log join tbl_task on tbl_task.task_id = tbl_log.task_id
              where tbl_log.username = '''+@username+'''
              and tbl_log.sprint_id = '''+@sprintId+'''
              ) x
              pivot (sum(log_hours) for log_date in (' + @cols + ')) p

              select *
              from #tmp_result 
              union all
              SELECT '''','+@vert_total +', 
ISNULL (SUM([Total]),0) FROM #tmp_result
DROP TABLE #tmp_result'
-- PRINT 'Pivot Query '+@FinalQuery
EXECUTE(@query)

could you tag your question with the proper RDBMS? Looks like SQL Server — Thomas G, Apr 14 '16 at 06:16

score 0 · Answer 1 · edited May 23 '17 at 10:28

If I understand the issue correctly, then the issue is with the type of join in your query in the @query variable

'select task_description as TASK,' + @cols + ',' + @horiz_total + ' as Total into #tmp_result from (select task_description, log_date, log_hours from tbl_log join tbl_task on tbl_task.task_id = tbl_log.task_id where tbl_log.username = '''+@username+''' and tbl_log.sprint_id = '''+@sprintId+'''

The JOIN is exclusive, so if there is no tasks it will exclude the records in the tbl_log.

To display the values from tbl_log regardless of havng corresponding records in tbl_task the join should be changed to inclusive, in your case this should be a LEFT OUTER JOIN

so the code should look like

'select task_description as TASK,' + @cols + ',' + @horiz_total + ' as Total 
              into #tmp_result
              from (select task_description, log_date, log_hours from tbl_log LEFT OUTER JOIN tbl_task on tbl_task.task_id = tbl_log.task_id
              where tbl_log.username = '''+@username+'''
              and tbl_log.sprint_id = '''+@sprintId+'''

You can omit the OUTER from LEFT OUTER JOIN

here is a link to nice explanation of different types of joins

SQL JOIN and different types of JOINs

I hope this helps.

Hi, Thanks for answering but unfortunately what I want is to display the dates (dates from sprint_start_date to sprint_end_date) and task on the pivot table even if they have no record on tbl_log — Dave, Apr 14 '16 at 11:37
Is there a table called tbl_sprint with the start and end dates ? Sorry it is not clear from the code, what is the data structure you are working with. — Dariusz Bielak, Apr 14 '16 at 11:50

Dynamic Gridview with Pivot Table

1 Answers1