Use a recursive CTE:
DECLARE @id INT
SET @id = 3
;WITH hierarchy AS (
SELECT t.id, t.parentid
FROM YOUR_TABLE t
WHERE t.id = @id
UNION ALL
SELECT x.id, x.parentid
FROM YOUR_TABLE x
JOIN hierarchy h ON h.parentid = x.id)
SELECT h.id
FROM hierarchy h
Results:
id
---
3
2
1
More Related Contents:
- Insert results of a stored procedure into a temporary table
- How can I do an UPDATE statement with JOIN in SQL Server?
- How to update two tables in one statement in SQL Server 2005?
- Create a date from day month and year with T-SQL
- How important is the order of columns in indexes?
- How to return multiple values in one column (T-SQL)?
- Using the result of an expression (e.g. Function call) in a stored procedure parameter list?
- SQL – Subtracting a depleting value from rows
- Drop all the tables, stored procedures, triggers, constraints and all the dependencies in one sql statement
- For Nvarchar(Max) I am only getting 4000 characters in TSQL?
- Is it possible to select sql server data using column ordinal position
- SELECT FOR UPDATE with SQL Server
- How do I group on continuous ranges
- How can a LEFT OUTER JOIN return more records than exist in the left table?
- “select * from table” vs “select colA, colB, etc. from table” interesting behaviour in SQL Server 2005
- Dynamic SQL to generate column names?
- Is it possible to perform multiple updates with a single UPDATE SQL statement?
- Column name or number of supplied values does not match table definition
- Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
- How to split string using delimiter char using T-SQL?
- How to find consecutive rows based on the value of a column?
- SQL server select distinct rows using most recent value only
- How to escape square brackets inside square brackets for field name
- How do you copy a record in a SQL table but swap out the unique id of the new row?
- Eliminate and reduce overlapping date ranges
- Cumulative Total in MS Sql server [duplicate]
- Use variable with TOP in select statement in SQL Server without making it dynamic [duplicate]
- Compare dates in T-SQL, ignoring the time part
- Memory effective way to read BLOB data in C#/SQL 2005
- When to use EXCEPT as opposed to NOT EXISTS in Transact SQL?