October 15, 2011 at 12:43 am
I have the following table:
CREATE TABLE TABLE1(PARENT_ID VARCHAR(10),CHILD_ID VARCHAR(10))
INSERT INTO TABLE1 SELECT '0','39285'
INSERT INTO TABLE1 SELECT '0','56579'
INSERT INTO TABLE1 SELECT '0','58244'
INSERT INTO TABLE1 SELECT '0','60227'
INSERT INTO TABLE1 SELECT '0','7433'
INSERT INTO TABLE1 SELECT '7433','7434'
INSERT INTO TABLE1 SELECT '7434','7435'
INSERT INTO TABLE1 SELECT '7434','7438'
INSERT INTO TABLE1 SELECT '7434','7444'
INSERT INTO TABLE1 SELECT '7435','7436'
INSERT INTO TABLE1 SELECT '7438','7439'
INSERT INTO TABLE1 SELECT '7438','7441'
INSERT INTO TABLE1 SELECT '7444','7445'
INSERT INTO TABLE1 SELECT '7444','7448'
INSERT INTO TABLE1 SELECT '7445','7446'
INSERT INTO TABLE1 SELECT '7445','7447'
INSERT INTO TABLE1 SELECT '7448','7449'
INSERT INTO TABLE1 SELECT '7439','7440'
INSERT INTO TABLE1 SELECT '7441','7442'
INSERT INTO TABLE1 SELECT '7441','7443'
INSERT INTO TABLE1 SELECT '7436','7437'
I want to convert the given table into following table without using any other intermediate table:
0 39285
0 56579
0 58244
0 60227
0 7433 7434 7435 7436 7437
0 7433 7434 7438 7439 7440
0 7433 7434 7438 7441 7442
0 7433 7434 7438 7441 7443
0 7433 7434 7444 7445 7446
0 7433 7434 7444 7445 7447
0 7433 7434 7444 7448 7449
i.e. I am trying to flatten out the data..but dont know why this is killing me..my brain is cranking up here..pls try to help guys..thanks in advance
October 15, 2011 at 1:32 am
I want to convert the given table into following table without using any other intermediate table:
0 39285
0 56579
0 58244
0 60227
0 7433 7434 7435 7436 7437
0 7433 7434 7438 7439 7440
0 7433 7434 7438 7441 7442
0 7433 7434 7438 7441 7443
0 7433 7434 7444 7445 7446
0 7433 7434 7444 7445 7447
0 7433 7434 7444 7448 7449
What is the logic of flattening the data?
October 15, 2011 at 1:46 am
You could pivot the result of a rCTE but this is easier to code and tons easier to get your head around:
SELECT n1.PARENT_ID, n1.CHILD_ID, n2.CHILD_ID, n3.CHILD_ID, n4.CHILD_ID, n5.CHILD_ID
FROM TABLE1 n1
LEFT JOIN TABLE1 n2 ON n2.PARENT_ID = n1.CHILD_ID
LEFT JOIN TABLE1 n3 ON n3.PARENT_ID = n2.CHILD_ID
LEFT JOIN TABLE1 n4 ON n4.PARENT_ID = n3.CHILD_ID
LEFT JOIN TABLE1 n5 ON n5.PARENT_ID = n4.CHILD_ID AND n5.PARENT_ID > 0
WHERE n1.PARENT_ID = 0
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 15, 2011 at 2:42 am
Thank you for the reply guys..I really appreciate that..I have got the result using following query:
SELECT 0,
lev01.CHILD_ID id_01,
lev02.CHILD_ID id_02,
lev03.CHILD_ID id_03,
lev04.CHILD_ID id_04,
lev05.CHILD_ID id_05
FROM PEGGING lev01
LEFT OUTER JOIN PEGGING lev02 ON lev01.CHILD_ID = lev02.parent_id
LEFT OUTER JOIN PEGGING lev03 ON lev02.CHILD_ID = lev03.parent_id
LEFT OUTER JOIN PEGGING lev04 ON lev03.CHILD_ID = lev04.parent_id
LEFT OUTER JOIN PEGGING lev05 ON lev04.CHILD_ID = lev05.parent_id
WHERE lev01.parent_id =0;
which can be dynamically written as:
DECLARE @MAX_LEVELS INT,@I INT,@CMD VARCHAR(MAX)
--SET @MAX_LEVELS = SELECT MAX(LEVEL_ID) FROM TABLE1
SET @MAX_LEVELS = 18
SET @I=1
SET @CMD = 'SELECT 0, '
WHILE @I<@MAX_LEVELS
BEGIN
SET @CMD = @CMD + 'lev'+CAST(@I AS VARCHAR(MAX))+'.CHILD_ID id_'+CAST(@I AS VARCHAR(MAX))+','
SET @I=@I+1
END
SET @CMD = @CMD + 'lev'+CAST(@MAX_LEVELS AS VARCHAR(MAX))+'.CHILD_ID id_'+CAST(@MAX_LEVELS AS VARCHAR(MAX))
SET @CMD = @CMD + ' FROM TABLE1 lev1'
--+CAST(@I AS VARCHAR(MAX))
SET @I=2
WHILE @I<@MAX_LEVELS
BEGIN
SET @CMD = @CMD + ' LEFT OUTER JOIN TABLE1 lev'+CAST(@I AS VARCHAR(MAX))+' ON lev'+CAST((@I-1) AS VARCHAR(MAX))+'.CHILD_ID = lev'++CAST(@I AS VARCHAR(MAX))+'.parent_id'
SET @I=@I+1
END
SET @CMD = @CMD + ' LEFT OUTER JOIN TABLE1 lev'++CAST(@MAX_LEVELS AS VARCHAR(MAX))+ ' ON lev'+CAST((@I-1) AS VARCHAR(MAX))+'.CHILD_ID = lev'++CAST(@MAX_LEVELS AS VARCHAR(MAX))+'.parent_id'
SET @CMD = @CMD + ' WHERE lev1.parent_id =0'
PRINT @CMD
But the query is taking loooooooooong time for millions of records..any way to optimize it?
October 15, 2011 at 5:29 am
An index containing childid and parentid (and nothing else).
You could also consider creating the clustered index over these two columns - unique if possible.
What other columns are there in the table?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
October 15, 2011 at 10:04 am
same topic discussion is there in another thread of SSC just have a look on it.
http://qa.sqlservercentral.com/Forums/Topic1146957-392-1.aspx
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply