T-SQL query - 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 2008 Forums

Transact-SQL (2008)

T-SQL query

Author

Topic

mailtobillu
Starting Member

3 Posts

Posted - 2013-04-17 : 04:05:21

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
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-04-17 : 09:22:33

Is there any other columns in the table?

--
Chandu

djj55
Constraint Violating Yak Guru

352 Posts

Posted - 2013-04-17 : 13:02:00

Are there always four rows with sequential IDs?

djj

mailtobillu
Starting Member

3 Posts

Posted - 2013-04-17 : 21:53:42

@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?

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2013-04-18 : 00:51:45

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!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-04-18 : 15:01:12

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

mailtobillu
Starting Member

3 Posts

Posted - 2013-04-18 : 21:24:57

@Lamprey - It really works. Thanks a lot.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources