column to row - SQL Server Forums

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]

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?

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)

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.

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources