SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 column to row
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

leostrut
Starting Member

3 Posts

Posted - 03/29/2013 :  02:59:02  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/29/2013 :  03:30:09  Show Profile  Reply with Quote

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
Go to Top of Page

leostrut
Starting Member

3 Posts

Posted - 03/29/2013 :  04:52:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/29/2013 :  05:02:20  Show Profile  Reply with Quote
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 - 03/31/2013 :  22:27:13  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 04/01/2013 :  00:27:35  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000