DateTime Rows to Columns

Question

DateTime Rows to Columns

rootfixxxer

SSCrazy

Points: 2918
More actions
March 1, 2013 at 11:06 am

#271737

Hi
I have something like a table of users and other table with dates and working time for the users, i want to display the working hours for the users, but i need to see the date in columns.
I created a little script to show what i want, but i'm having a problem with the duplicate users.
DROP TABLE #Users
DROP TABLE #Working
CREATE TABLE #Users (Num INT, NAME NVARCHAR(5))
CREATE TABLE #Working (WorkDay SMALLDATETIME,Num INT, TimeWorked INT)
INSERT INTO #Users
SELECT 1,'User1'
UNION ALL
SELECT 2,'User2'
UNION ALL
SELECT 3,'User3'
INSERT INTO #Working
SELECT '01/01/2013',1,2
UNION ALL
SELECT '02/01/2013',1,0
UNION ALL
SELECT '03/01/2013',1,1
UNION ALL
SELECT '04/01/2013',1,4
UNION ALL
SELECT '05/01/2013',1,0
UNION ALL
SELECT '01/01/2013',2,0
UNION ALL
SELECT '02/01/2013',2,0
UNION ALL
SELECT '03/01/2013',2,20
UNION ALL
SELECT '04/01/2013',2,20
UNION ALL
SELECT '05/01/2013',2,20
UNION ALL
SELECT '01/01/2013',3,30
UNION ALL
SELECT '02/01/2013',3,30
UNION ALL
SELECT '03/01/2013',3,0
UNION ALL
SELECT '04/01/2013',3,0
UNION ALL
SELECT '05/01/2013',3,0
SELECT * FROM #Users
SELECT * FROM #Working
SELECT DISTINCT
U.Num,
U.NAME,
[1] = CASE WHEN MONTH(W.WorkDay) = 1 THEN W.TimeWorked ELSE 0 END,
[2] = CASE WHEN MONTH(W.WorkDay) = 2 THEN W.TimeWorked ELSE 0 END,
[3] = CASE WHEN MONTH(W.WorkDay) = 3 THEN W.TimeWorked ELSE 0 END,
[4] = CASE WHEN MONTH(W.WorkDay) = 4 THEN W.TimeWorked ELSE 0 END,
[5] = CASE WHEN MONTH(W.WorkDay) = 5 THEN W.TimeWorked ELSE 0 END
FROM #Users U INNER JOIN #Working W ON U.Num = W.Num
In the end the query will be built on the fly, with the help of the tally table, i just pass 2 paramteres to the sp, the year and the month...
Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Erin Ramsay SSCrazy Eights Points: 9728 More actions · Answer 1

The request is a little unclear to me. Can you supply a sample of what you would expect from the resultset so we can understand further?

rootfixxxer SSCrazy Points: 2918 More actions · Answer 2

Hi

I have to reply with text, in this machine i don't have any sql server installed.

So if i run the above code i get something like this:

Num Name 1 2 3 4 5 ---> Column Names

1 User1 2 0 0 0 0

1 User1 0 0 0 0 0

1 User1 0 0 1 0 0

1 User1 0 0 0 4 0

1 User1 0 0 0 0 0

and something equal for the other users...

WHat i'm trying to achieve it's to only have one row for each user like this:

Num Name 1 2 3 4 5 ---> Column Names

1 User1 2 0 1 4 0

Jeff Moden SSC Guru Points: 1004303 More actions · Answer 3

You were SO close...

SELECT

U.Num,

U.NAME,

[1] = SUM(CASE WHEN MONTH(W.WorkDay) = 1 THEN W.TimeWorked ELSE 0 END),

[2] = SUM(CASE WHEN MONTH(W.WorkDay) = 2 THEN W.TimeWorked ELSE 0 END),

[3] = SUM(CASE WHEN MONTH(W.WorkDay) = 3 THEN W.TimeWorked ELSE 0 END),

[4] = SUM(CASE WHEN MONTH(W.WorkDay) = 4 THEN W.TimeWorked ELSE 0 END),

[5] = SUM(CASE WHEN MONTH(W.WorkDay) = 5 THEN W.TimeWorked ELSE 0 END)

FROM #Users U INNER JOIN #Working W ON U.Num = W.Num

GROUP BY U.Num, U.Name;

You can learn more about this technique (cross tab) at the following link.

http://www.sqlservercentral.com/articles/T-SQL/63681/

And thank you VERY much for the test data setup and the sample code. It made helping you very easy to do. I wish more people would take the small bit of time.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

rootfixxxer SSCrazy Points: 2918 More actions · Answer 4

:blush:

End of the day of friday, hard week... dumb questions... :satisfied:

Now it's obvious that i need to sum the values... 😀

Thanks