Click here to Skip to main content
15,902,198 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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.
HTML
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


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

HTML
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

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
Posted
Updated 16-Aug-12 4:47am
v8

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.


SQL
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:
SQL
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]
 
Share this answer
 
Comments
bubai banerjee 16-Aug-12 8:34am    
Dear Anuja Madam,
Thanks for your reply.Actually username are coming from databases. User Name should be dynamic. May be lot of users.so please give me dynamic solution madam so that it can sort out my problem.Lot of Thanks in advance.
Thanks and Regards
Bubai Banerjee
Go through this link. Detail description with example given here.
http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/
 
Share this answer
 
Comments
bubai banerjee 16-Aug-12 8:32am    
Thanks for your reply.this is what i am not really wanted.it is not matching with my expectation.Give me some practicle hints as i am very new in the system.Please help me with my example please.
hope u can understand my situation.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900