Create Columns From Table Rows

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

General SQL Server Forums

New to SQL Server Programming

Create Columns From Table Rows

Author

Topic

dotnet2010
Starting Member

7 Posts

Posted - 2011-08-02 : 03:49:56

Hi,
I have this table as result of selectQuery
Every Id can return more than one time at different Datetime
the table is sorted by ID,DateTime
Every id can have three events max 1,4,3
Id DateTime Event Value1 Value2
--------------------------------------------------
30 2011-08-01 16:06:03.000 1 0 0
30 2011-08-01 16:06:34.000 4 31 19
30 2011-08-01 16:06:34.000 3 5 0
30 2011-08-01 16:07:08.000 1 0 0
28 2011-08-01 16:07:14.000 4 1087 5
30 2011-08-01 16:07:20.000 4 12 5
31 2011-08-01 16:08:01.000 1 40 19
31 2011-08-01 16:09:01.000 4 40 19
31 2011-08-01 16:09:01.000 3 22 0
31 2011-08-01 16:10:00.000 1 0 0
31 2011-08-01 16:11:03.000 4 62 22
31 2011-08-01 16:11:03.000 3 19 0
31 2011-08-01 16:11:41.000 1 0 0
31 2011-08-01 16:12:12.000 4 31 19

Every time I see the Event 4 I want to add new columns (left join or new table) to add the DateTime of the Event 1 that present one row before it if EXISTS else the value will be the same date time that presents at Event 4

For Example the last row will be like this
ID DateTime Event V1 V2 DateTime2
31 2011-08-01 16:12:12.000 4 31 19 2011-08-01 16:11:41.000

The row at Id 29 will be like this
Id DateTime Event Value1 Value2 DateTime2
28 2011-08-01 16:07:14 4 1087 5 2011-08-01 16:07:14

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-02 : 04:39:28

so what is the DateTime2 for this record
"30 2011-08-01 16:07:20.000 4 12 5"

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 07:09:58

see scenario 2 here

http://visakhm.blogspot.com/2010/01/multipurpose-apply-operator.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

dotnet2010
Starting Member

7 Posts

Posted - 2011-08-02 : 07:15:58

quote:
Originally posted by khtan

so what is the DateTime2 for this record
"30 2011-08-01 16:07:20.000 4 12 5"

KH
[spoiler]Time is always against us[/spoiler]

Since there is no Event 1 Before it The datetime2 will be the same
as the datetime1
"30 2011-08-01 16:07:20.000 4 12 5 2011-08-01 16:07:20.000"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-02 : 07:22:46

[code]select t.Id,t.DateTime,t.Event,t.Value1,t.Value2,
case when t.event=4 and t2.cnt=0 and t1.Datetime is not null then t1.Datetime else t.Datetime end as datetime2
from table t
outer apply(select top 1 datetime
from table
where id=t.id
and DateTime <t.DateTime
and Event = 1
order by DateTime desc)t1
outer apply(select count(*) as cnt
from table
where DateTime between t1.datetime and t.DateTime
and id<> t.id
and event <> t.event
)t2
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources