Result in Pivot Structure

Question

I am sending you the details that i am in mess. below I am describing.

Original Table Structure.

ID                Date             TimeLogged(Hrs)                UserName
1             10/8/2012               5.50                          Bubai
2             11/8/2012               2.30                          Bubai
3             10/8/2012               3.30                          Bhanu
4             11/8/2012               7.30                          Bhanu

I want result like below. User Name should be dynamic. May be lot of users. User name will come from Database table. I want to show details in Gridview(Front End).explain broadly as I am very new in development.

Date                   Bubai               Bhanu                  Total
10/8/2012              5.30                 3                     8. 30
11/8/2012              2.30                 7.30                  10
  Total                8                    10.30                 18.30

Answer 1

You can use a PIVOT for this, either a Static or Dynamic. You can place this code in a stored procedure and populate your datagrid with it.

Static Pivot (See SQL Fiddle with Demo) This means you will hard code all values:

select convert(char(10), dt, 101), [Bubai], [Bhanu], ([Bubai] + [Bhanu]) total
from
(
    select dt, timelogged, username
    from test
)x
pivot
(
    sum(timelogged)
    for username in ([Bubai], [Bhanu])
)p
union all
select 'total', sum([Bubai]), sum([Bhanu]), sum([Bubai] +[Bhanu])
from
(
    select dt, timelogged, username
    from test
)x
pivot
(
    sum(timelogged)
    for username in ([Bubai], [Bhanu])
)p

Dynamic Pivot (See SQL Fiddle with Demo), this will get the list of fields to transform at run-time:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX),
    @totalCol AS NVARCHAR(MAX),
    @totalRow AS NVARCHAR(MAX)

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.username) 
            FROM test c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @totalCol = STUFF((SELECT distinct '+' + QUOTENAME(c.username) 
            FROM test c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

SET @totalRow = STUFF((SELECT distinct ',Sum(' + QUOTENAME(c.username) + ')'
            FROM test c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')



set @query = 'SELECT convert(char(10), dt, 101), ' + @cols + ', '+@totalCol +' total from 
            (
                select dt, timelogged, username
                from test
           ) x
            pivot 
            (
                sum(timelogged)
                for username in  (' + @cols + ')
            ) p 
            union all
            select ''total'', '+ @totalRow +', sum('+@totalCol+')
            from
            (
                select dt, timelogged, username
                from test
            )x
            pivot
            (
                sum(timelogged)
                for username in (' + @cols + ')
            )p'


execute(@query)

Both of these will produce the same results.

Answer 2

@NikolaMarkovinović is right, you should get the results you need using a pivot query, the problem is you have to know the values in the column you want to pivot

SELECT Date , [Bunbai] , [Bhanu] , ..., /* This names have to be known, the same as in the IN part of the PIVOT */
/* You can even do this */
[Bunbai]+[Bhanu] AS Total
FROM ( <SELECT query that produces the data> ) AS T
PIVOT ( SUM( TimeLoggedHours ) FOR UserName
IN ( [Bunbai] , [Bhanu] , ... ) 
/* You can't write some subquery inside the IN, columns names have to be known */
) AS pvt

, you will need to write a Dinamic Query Method in you code to, first get the names of the columns, then add them in the headers of the query and the IN part of the PIVOT.

To add the totals at the bottom just write the same query but in the query that produces the data, instead of the date select 'Total' and use UNION ALL

Hope this helps.