December 30, 2009 at 7:07 am
I have one huge table with binary structure. In it a record can have maximum of two child records.
Now my task is to assign a Side to every child record. The First child of a record must be assigned to Left side (L) of the parent ID, and the Second child must be assigned to the Right side (R) of the parent ID. If the Parent record has only one child record, it must be assigned to Left side (L) of the parent ID.
See the sample Table (#MyBinary) below
ID | ParentID | Side
1 | NULL | NULL
2 | 1 | NULL
3 | 1 | NULL
4 | 2 | NULL
5 | 2 | NULL
6 | 3 | NULL
7 | 4 | NULL
8 | 5 | NULL
9 | 5 | NULL
10 | 6 | NULL
The Output of the Update Query should be:
IDParentIDSide
1 | NULL | NULL
2 | 1 | L
3 | 1 | R
4 | 2 | L
5 | 2 | R
6 | 3 | L
7 | 4 | L
8 | 5 | L
9 | 5 | R
10 | 6 | L
The Code for the above sample table:
-- Create Table
CREATE TABLE #MyBinary(
ID INT,
ParentID INT,
Side CHAR(1)
)
-- Insert Data
INSERT INTO #MyBinary VALUES (1, NULL, NULL)
INSERT INTO #MyBinary VALUES (2, 1, NULL)
INSERT INTO #MyBinary VALUES (3, 1, NULL)
INSERT INTO #MyBinary VALUES (4, 2, NULL)
INSERT INTO #MyBinary VALUES (5, 2, NULL)
INSERT INTO #MyBinary VALUES (6, 3, NULL)
INSERT INTO #MyBinary VALUES (7, 4, NULL)
INSERT INTO #MyBinary VALUES (8, 5, NULL)
INSERT INTO #MyBinary VALUES (9, 5, NULL)
INSERT INTO #MyBinary VALUES (10, 6, NULL)
I assume this can be done using an Update Query to update the Side column. I tried, but could not figure it out.
Right now I am using Stored Procedure, which uses cursor to update the Side column. It takes lot of time.
Even a small hint to the solution can be of great help.
December 30, 2009 at 7:12 am
Something like this ?
with cteBinary
as
(
Select Id,ParentId,ROW_NUMBER() over ( Partition by ParentId order by Id) as SideInt
from #MyBinary
)
select Id,ParentId,case when SideInt = 1 then 'L' else 'R' end as Side
From cteBinary
December 30, 2009 at 7:13 am
This should help you write an update statement...
-- Create Table
declare @MyBinary table(
ID INT,
ParentID INT,
Side CHAR(1)
)
-- Insert Data
INSERT INTO @MyBinary VALUES (1, NULL, NULL)
INSERT INTO @MyBinary VALUES (2, 1, NULL)
INSERT INTO @MyBinary VALUES (3, 1, NULL)
INSERT INTO @MyBinary VALUES (4, 2, NULL)
INSERT INTO @MyBinary VALUES (5, 2, NULL)
INSERT INTO @MyBinary VALUES (6, 3, NULL)
INSERT INTO @MyBinary VALUES (7, 4, NULL)
INSERT INTO @MyBinary VALUES (8, 5, NULL)
INSERT INTO @MyBinary VALUES (9, 5, NULL)
INSERT INTO @MyBinary VALUES (10, 6, NULL)
select Id
,ParentID
,case
when parentid is null then null
when row_number() over (partition by parentid order by id) = 1 then 'L'
else 'R'
end
from @mybinary
December 30, 2009 at 7:28 am
Thanks Dave and Dave and for quick replies
Being a greedy person, I hope to get more solutions from forum members to help expand my knowledge horizon. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply