数据透视格式的结果 [英] Result in Pivot format

问题描述

您好，先生，
我怀疑您能不能帮助我..我在S/W开发中是一个新手.
我在下面描述.
原始表结构.

Hi Sir,
I have doubt can you please help me..I am very new in s/w DEvelopment.
below i am describing.
Original Table Structure.

ID                Date                TimeLogged                 UserName
1                10/8/2012               5.30                      Bubai
2                11/8/2012               2.30                      Bubai
3                10/8/2012               3.00                      Bhanu
4                11/8/2012               7.30                      Bhanu

我想要如下结果.用户名应该是动态的.可能很多
用户.用户名将来自数据库表.请给我广泛的
描述，因为我是系统中的新手，以我的理解.
我必须在Gridview中显示结果

I want result like below. User Name should be dynamic. May be lot of
users. User name will come from Database table. Please give me broad
description as I am very new in the system, for my understanding.
i have to show result in Gridview

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

请帮助我解决这个问题... 这非常紧急
如果您能及早答复，我将不胜感激.提前谢谢.

谢谢与问候
Bubai Banerjee

Please help me to solve this issue… It’s really urgent
if you can reply early I’ll really appreciate. Thanks in advance.

Thanks and Regards
Bubai Banerjee

推荐答案

请在下面找到立即解决方案，灵活度为零.

缺点:需要为不同的用户名写出尽可能多的MAX(CASE WHEN = ...).如果用户名超过10个，或者将来可能有更多用户名，则不灵活.

正确的解决方案是重新设计表和/或数据条目.

Please find an immediate solution below that''s zero percent flexible.

Disadvantage: Need to write as many MAX(CASE WHEN =...) as to distinct username. Not flexible if userNames are more than 10, or can have more UserNames in future.

The right solution would be to redesign table and/or data entry.

SELECT
  [Date]
, MAX(CASE WHEN UserName = 'Bubai' THEN TimeLogged END) AS [Bubai]
, MAX(CASE WHEN UserName = 'Bhanu' THEN TimeLogged END) AS [Bhanu]
, SUM(TimeLogged) AS Total
FROM [YourTable]
GROUP BY [Date]

要进行测试，也可以使用以下代码:

For testing use this too:

SELECT
  [Date]
, MAX(CASE WHEN UserName = 'Bubai' THEN TimeLogged END) AS [Bubai]
, MAX(CASE WHEN UserName = 'Bhanu' THEN TimeLogged END) AS [Bhanu]
, SUM(TimeLogged) AS Total
FROM
(
SELECT 1 as ID, '10/8/2012' as Date, 5.50 as TimeLogged, 'Bubai' AS UserName
UNION ALL
SELECT 2 as ID, '11/8/2012' as Date, 2.30 as TimeLogged, 'Bubai' AS UserName
UNION ALL
SELECT 3 as ID, '10/8/2012' as Date, 3.30 as TimeLogged, 'Bhanu' AS UserName
UNION ALL
SELECT 4 as ID, '11/8/2012' as Date, 7.30 as TimeLogged, 'Bhanu' AS UserName
)AS X
GROUP BY [Date]

浏览此链接.此处给出示例的详细说明.
http://blog.sqlauthority.com/2008/06 /07/sql-server-pivot-and-unpivot-table-examples/

Go through this link. Detail description with example given here.
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/

这篇关于数据透视格式的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！