我正在向您发送我陷入混乱的详细信息。下面我描述。
原始表结构。
ID 日期 时间记录(小时) 用户名1 2012/10/8 5.50 步白2 2012/11/8 2.30 步白3 2012/10/8 3.30 巴努4 2012/11/8 7.30 巴努
我想要如下结果。用户名应该是动态的。可能很多用户。用户名将来自数据库表。我想在 Gridview(前端)中显示详细信息。广泛解释,因为我在开发方面非常新手。
日期 布拜巴努 总计10/8/2012 5.30 3 8.3011/8/2012 2.30 7.30 10 共8页 10.30 18.30
您可以使用静态或动态PIVOT
。可以将此代码放在存储过程中,并用它填充数据网格。
静态透视(参见 SQL 小提琴演示) 这意味着您将对所有值进行硬编码:
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
动态透视(参见 SQL Fiddle with Demo),这将获取要在运行时转换的字段列表:
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)
这两者都会产生相同的结果。
@NikolaMarkovinović是正确的,您应该使用透视查询获得所需的结果,问题是您必须知道要透视的列中的值
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
,您需要在代码中编写一个 Dinamic 查询方法,首先获取列的名称,然后将它们添加到查询的标头和 PIVOT 的 IN 部分中。
要在底部添加总计,只需编写相同的查询,但在生成数据的查询中,选择"总计"而不是日期并使用 UNION ALL
希望这有帮助。