change records to two column result

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 2005 Forums

Transact-SQL (2005)

change records to two column result

Author

Topic

smithersgs
Starting Member

17 Posts

Posted - 2008-08-26 : 16:25:28

A table has two fields named Types and Time. There are two different types, Catalog and Application. I want to group them by time (date), but one more. If they have same date I want to have them in the same row such as:

row1: Catalog 8/8/08 10:00:01 Application 8/8/08 9:10:12
row2: Catalog 8/8/08 11:12:32 Application 8/8/08 12:34:44
row3: Catalog 8/8/08 11:13:11

row4: Catalog 8/10/08 07:11:32 Application 8/10/08 09:09:22

Can anyone help me to write SQL for this purpose?

Thanks much,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-26 : 23:48:42

[code]SELECT MAX(CASE WHEN Types='Category' THEN Types ELSE NULL END),
MAX(CASE WHEN Types='Category' THEN Time ELSE NULL END),
MAX(CASE WHEN Types='Application' THEN Types ELSE NULL END),
MAX(CASE WHEN Types='Application' THEN TimeELSE NULL END)
FROM
(SELECT ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Time),
Types,Time
FROM YourTable)t
GROUP BY t.Seq[/code]

smithersgs
Starting Member

17 Posts

Posted - 2008-08-27 : 10:07:32

visakh16,

Thanks for the reply. I tried after changing the actual field names and the table, then I got the following error. Can you tell me what they are?

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 't'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Seq'.

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 10:09:40

quote:
Originally posted by smithersgs

visakh16,

Thanks for the reply. I tried after changing the actual field names and the table, then I got the following error. Can you tell me what they are?

Msg 8155, Level 16, State 2, Line 1
No column was specified for column 1 of 't'.
Msg 207, Level 16, State 1, Line 9
Invalid column name 'Seq'.

Thanks,

ah sorry missed that part

SELECT MAX(CASE WHEN Types='Category' THEN Types ELSE NULL END),
 MAX(CASE WHEN Types='Category' THEN Time ELSE NULL END),
 MAX(CASE WHEN Types='Application' THEN Types ELSE NULL END),
 MAX(CASE WHEN Types='Application' THEN TimeELSE NULL END) 
FROM 
(SELECT ROW_NUMBER() OVER(PARTITION BY Type ORDER BY Time) AS Seq,
Types,Time
FROM YourTable)t
GROUP BY t.Seq

smithersgs
Starting Member

17 Posts

Posted - 2008-08-27 : 12:22:30

Good, thanks again. I could run it. What does the MAX funtion do in your query? Because that is not exactly what I wanted.

Please take a look at the result below. There are 4 Catalogs on 5/12 and 12 Applications on the same day. I want to have 8 NULL in the Catalog columns. That is, the Catalog record of 5/13 started from the same row of Application reacord of 5/13.

Result for your query:
Catalog 5/12/2008 1:34:47 PM Application 5/12/2008 1:47:44 PM
Catalog 5/12/2008 2:22:05 PM Application 5/12/2008 10:15:04 PM
Catalog 5/12/2008 4:02:24 PM Application 5/12/2008 10:57:41 PM
Catalog 5/12/2008 6:39:08 PM Application 5/12/2008 11:08:23 PM
Catalog 5/13/2008 11:07:21 AM Application 5/12/2008 3:29:23 PM
Catalog 5/13/2008 12:34:02 PM Application 5/12/2008 3:45:31 PM
Catalog 5/13/2008 2:33:34 PM Application 5/12/2008 5:56:58 PM
Catalog 5/13/2008 3:15:25 PM Application 5/12/2008 7:08:09 PM
Catalog 5/13/2008 8:20:10 PM Application 5/12/2008 7:33:39 PM
Catalog 5/13/2008 9:14:45 PM Application 5/12/2008 8:46:53 PM
Catalog 5/14/2008 10:36:28 AM Application 5/12/2008 9:08:01 PM
Catalog 5/14/2008 4:13:31 PM Application 5/12/2008 9:19:13 PM
Catalog 5/14/2008 4:32:28 PM Application 5/13/2008 1:24:31 PM
Catalog 5/15/2008 10:55:50 AM Application 5/13/2008 10:48:11 PM
Catalog 5/15/2008 2:15:14 AM Application 5/13/2008 11:25:19 AM

What I need:
Catalog 5/12/2008 1:34:47 PM Application 5/12/2008 1:47:44 PM
Catalog 5/12/2008 2:22:05 PM Application 5/12/2008 10:15:04 PM
Catalog 5/12/2008 4:02:24 PM Application 5/12/2008 10:57:41 PM
Catalog 5/12/2008 6:39:08 PM Application 5/12/2008 11:08:23 PM
NULL NULL Application 5/12/2008 3:29:23 PM
NULL NULL Application 5/12/2008 3:45:31 PM
NULL NULL Application 5/12/2008 5:56:58 PM
... Application 5/12/2008 7:08:09 PM
... Application 5/12/2008 7:33:39 PM
... Application 5/12/2008 8:46:53 PM
... Application 5/12/2008 9:08:01 PM
... Application 5/12/2008 9:19:13 PM
Catalog 5/13/2008 11:07:21 AM Application 5/13/2008 1:24:31 PM
Catalog 5/13/2008 12:34:02 PM Application 5/13/2008 10:48:11 PM
Catalog 5/13/2008 2:33:34 PM Application 5/13/2008 11:25:19 AM

Thanks,

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 12:49:55

How will the NULLs be coming ? can you explain that with some table data?

smithersgs
Starting Member

17 Posts

Posted - 2008-08-27 : 13:39:58

What I meant is that is what I wanted. I need your more help. How can I get those NULL values there like that?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-27 : 13:48:42

quote:
Originally posted by smithersgs

What I meant is that is what I wanted. I need your more help. How can I get those NULL values there like that?

Ok. But first show the table data and explain how you think NULL values will come in output.

smithersgs
Starting Member

17 Posts

Posted - 2008-08-28 : 11:36:06

Data looks like this:

Catalog 5/12/2008 1:34:47 PM
Application 5/12/2008 1:47:44 PM
Catalog 5/12/2008 2:22:05 PM
Application 5/12/2008 3:29:23 PM
Application 5/12/2008 3:45:31 PM
Catalog 5/12/2008 4:02:24 PM
Catalog 5/12/2008 6:39:08 PM
Application 5/12/2008 5:56:58 PM
Application 5/12/2008 7:33:39 PM
Application 5/12/2008 7:08:09 PM
Application 5/12/2008 8:46:53 PM
Application 5/12/2008 9:08:01 PM
Application 5/12/2008 9:19:13 PM
Application 5/12/2008 10:15:04 PM
Application 5/12/2008 10:57:41 PM
Application 5/12/2008 11:08:23 PM
Application 5/13/2008 5:09:58 AM
Application 5/13/2008 7:39:41 AM
Catalog 5/13/2008 11:07:21 AM
Application 5/13/2008 11:25:19 AM
Catalog 5/13/2008 12:34:02 PM

There is no NULL. I am not sure what you meant by NULL, but hope this helps.

Thanks,

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources