Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 column to row

Author  Topic 

leostrut
Starting Member

3 Posts

Posted - 2013-03-29 : 02:59:02
I have selected serveral field from different tables and create a temp table1 like this:
Number loginSystemDate loginTime
A00001 1/2/2013 12:51:57
A00001 1/2/2013 12:01:00
A00001 1/2/2013 17:01:13
A00001 2/2/2013 18:01:13
A00002 6/2/2013 7:56:59
A00002 6/2/2013 12:00:44
A00004 1/2/2013 12:01:01
A00004 1/2/2013 17:00:44
A00004 1/2/2013 17:59:31
However i want to make it like the following table, what i have to do to create a second table from table 1 information?
Number loginSystemDate Between12and1pm [1] [2] [3]
A00001 1/2/2013 2 12:51:57 12:01:00 17:01:13
A00001 2/2/2013 0 18:01:13 null null
A00002 6/2/2013 1 7:56:59 12:00:44 null
A00004 1/2/2013 1 12:01:01 17:00:44 17:59:31

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 03:30:09
[code]
SELECT *
FROM (SELECT Number, loginSystemDate, logintime, COUNT(CASE WHEN loginTime BETWEEN '12:00:00' AND '13:01:00' THEN loginTime END) over() Between12and1pm,
row_number() OVER(PARTITION BY Number, loginSystemDate ORDER BY (SELECT 1)) Seq
FROM TempTable
)p
PIVOT (MAX(logintime) FOR Seq IN ([1], [2], [3]))pvt[/code]
Go to Top of Page

leostrut
Starting Member

3 Posts

Posted - 2013-03-29 : 04:52:15
your answer is helpful. After checked the sql, one column between12and1pm is always 27145 in each row.
anyone know why?

Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-29 : 05:02:20
oh am sorry.. its a typo
COUNT(CASE WHEN loginTime BETWEEN '12:00:00' AND '13:01:00' THEN loginTime END) over(PARTITION BY Number, loginSystemDate)
Go to Top of Page

leostrut
Starting Member

3 Posts

Posted - 2013-03-31 : 22:27:13
wow it works perfect. Can you give me some more advice to make it dynamic because users may login more than 3 times each day.
Go to Top of Page

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-01 : 00:27:35
For dynamic PIVOT follow this link....
http://beyondrelational.com/modules/2/blogs/88/Posts/14196/changing-rows-to-columns-using-pivot-dynamic-columns-for-pivoting-in-sql-server.aspx
http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx
Go to Top of Page
   

- Advertisement -