Member Avatar for milenio

Hi all,
I faced up the following problem:
I have a table:
ID|server | target | action
----------------------------
1 |a | b | attempt
2 empty row
3 |a | b | success
4 empty row
5 empty row
6 empty row
7 |b | a | attempt


Does someone have an idea how can select all rows ( no filtering with where clause and have the following result - instead of the empty fields on row e.g. 4 :to have the last nopt NULL values . e.g. row3:
3 |a | b | success
4 |a | b | success


Thanks to all.

Edited by milenio because: n/a
  • 2 Contributors
  • 1 Reply
  • 401 Views
  • 2 Days Discussion Span
  • Latest Post Latest Post by sknake
Member Avatar for sknake 
--Get a test environment set up
IF OBJECT_ID('tempdb..#Table', 'U') IS NOT NULL DROP TABLE #Table
Create Table #Table
(
  ID int PRIMARY KEY,
  [Server] varchar(10),
  [Target] varchar(10),
  [Action] varchar(10)
)
Insert Into #Table (ID, [Server], [Target], [Action]) Values (1, 'a', 'b', 'attempt')
Insert Into #Table (ID) Values (2)
Insert Into #Table (ID, [Server], [Target], [Action]) Values (3, 'a', 'b', 'success')
Insert Into #Table (ID) Values (4)
Insert Into #Table (ID) Values (5)
Insert Into #Table (ID) Values (6)
Insert Into #Table (ID, [Server], [Target], [Action]) Values (7, 'b', 'a', 'attempt')
--Here is the logic
Select
ID,
COALESCE
(
  [Server],
  (Select Top 1 [Server] From #Table x Where x.ID < #Table.ID and [Server] IS NOT NULL Order By Id Desc),
  'Unknown'
) As [Server],
COALESCE
(
  [Target],
  (Select Top 1 [Target] From #Table x Where x.ID < #Table.ID and [Target] IS NOT NULL Order By Id Desc),
  'Unknown'
) As [Target],
COALESCE
(
  [Action],
  (Select Top 1 [Action] From #Table x Where x.ID < #Table.ID and [Action] IS NOT NULL Order By Id Desc),
  'Unknown'
) As [Action]
From #Table
Order By ID
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.