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 2008 Forums
 Transact-SQL (2008)
 T-SQL query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mailtobillu
Starting Member

Malaysia
3 Posts

Posted - 04/17/2013 :  04:05:21  Show Profile  Reply with Quote
ID Description Value Primary Key
1993 SpotID 853780 NULL
1994 SpotAnchor 0 NULL
1995 SpotCopyAnchor 0 NULL
1996 SpotDescription Test NULL
1997 SpotID 853781 NULL
1998 SpotAnchor 0 NULL
1999 SpotCopyAnchor 0 NULL
2000 SpotDescription Test NULL
2001 SpotID 853782 NULL
2002 SpotAnchor 0 NULL
2003 SpotCopyAnchor 0 NULL
2004 SpotDescription Test NULL

From the above table, i need to manipulate the data in below way:

ID Description Value Primary Key
1993 SpotID 853780 853780
1994 SpotAnchor 0 853780
1995 SpotCopyAnchor 0 853780
1996 SpotDescription Test 853780
1997 SpotID 853781 853781
1998 SpotAnchor 0 853781
1999 SpotCopyAnchor 0 853781
2000 SpotDescription Test 853781
2001 SpotID 853782 853782
2002 SpotAnchor 0 853782
2003 SpotCopyAnchor 0 853782
2004 SpotDescription Test 853782

Can anybody help me out.

bandi
Flowing Fount of Yak Knowledge

India
2218 Posts

Posted - 04/17/2013 :  09:22:33  Show Profile  Reply with Quote
Is there any other columns in the table?

--
Chandu
Go to Top of Page

djj55
Constraint Violating Yak Guru

USA
337 Posts

Posted - 04/17/2013 :  13:02:00  Show Profile  Reply with Quote
Are there always four rows with sequential IDs?

djj
Go to Top of Page

mailtobillu
Starting Member

Malaysia
3 Posts

Posted - 04/17/2013 :  21:53:42  Show Profile  Reply with Quote
@bandi - The above one is just a temp table only. we can customize as per our logic
@djj55 - No, it could be vary from scenario. However, next set of items can be determine through SpotID.

I believe, we can achieve it through cursor as below :
DECLARE @PrimaryKey INT,@Id INT,@DescriptionText VARCHAR(50),@Value VARCHAR(MAX)
DECLARE db_cursor CURSOR FOR
SELECT Id,DescriptionText,Value
FROM #AuditDetail

SET @PrimaryKey = 'SpotID'

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @Id,@DescriptionText,@Value

WHILE @@FETCH_STATUS = 0
BEGIN
IF @DescriptionText = @PrimaryKey
BEGIN
SET @CurrentPrimaryKeyValue = CAST(@Value AS INT)
END
UPDATE #AuditDetail SET PrimaryKeyValue=@CurrentPrimaryKeyValue WHERE Id=@Id

FETCH NEXT FROM db_cursor INTO @Id,@DescriptionText,@Value
END

CLOSE db_cursor
DEALLOCATE db_cursor

However, i am looking to resolve it through sql query itself.

Can anybody suggest me to resolve this?

Go to Top of Page

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 04/18/2013 :  00:51:45  Show Profile  Reply with Quote
What are the rules? It looks like "replace any null 'value' or 'primary key' with the highest non-zero value from 'Value' or 'Primary Key' where ID is less than the row being processed.
If you'd been bothered to write some sample create table and insert statements, I'd have written you some SQL, but you didn't, so I won't!
Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4614 Posts

Posted - 04/18/2013 :  15:01:12  Show Profile  Reply with Quote
Here is one way (assuming I understand the rules) and with data-in-a-consumable-format goodness:
DECLARE @Foo TABLE 
(
	ID INT, 
	Description VARCHAR(50), 
	Value VARCHAR(20), 
	PrimaryKey VARCHAR(20)
)

INSERT @Foo VALUES 
(1993, 'SpotID', '853780', NULL),
(1994, 'SpotAnchor', '0', NULL),
(1995, 'SpotCopyAnchor', '0', NULL),
(1996, 'SpotDescription', 'Test', NULL),
(1997, 'SpotID', '853781', NULL),
(1998, 'SpotAnchor', '0', NULL),
(1999, 'SpotCopyAnchor', '0', NULL),
(2000, 'SpotDescriptionv', 'Test', NULL),
(2001, 'SpotID', '853782', NULL),
(2002, 'SpotAnchor', '0', NULL),
(2003, 'SpotCopyAnchor', '0', NULL),
(2004, 'SpotDescription', 'Test', NULL)

SELECT 
	*
FROM
(
	SELECT 
		A.ID,
		A.Description,
		A.Value,
		CASE WHEN B.Description = 'SpotID' THEN B .Value ELSE NULL END AS PrimaryKey,
		ROW_NUMBER() OVER (PARTITION BY A.ID ORDER BY B.ID DESC, A.ID DESC) AS RowNum	
	FROM 
		@Foo AS A
	INNER JOIN 
		@Foo AS B
		ON B.ID <= A.ID
		AND B.Description = 'SpotID'
) AS Temp 
WHERE RowNum = 1
Go to Top of Page

mailtobillu
Starting Member

Malaysia
3 Posts

Posted - 04/18/2013 :  21:24:57  Show Profile  Reply with Quote
@Lamprey - It really works. Thanks a lot.
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.09 seconds. Powered By: Snitz Forums 2000