converting recursive implementation to a flat one

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 2005 Forums

Transact-SQL (2005)

converting recursive implementation to a flat one

Author

Topic

Mehdi
Yak Posting Veteran

64 Posts

Posted - 2009-01-17 : 04:38:06

Hi,
I have a table

GOODID              Type               FGOODID

1                ElectricalSets         null
2                Satelite               1
3                Satelite Dish          2
4                CottonProducts         null
5                Satelite Receiver      2

now I want to have a query(view) which gets an output
like:

1  ElectricalSets  2  Satelite   3   Satelite Dish
1  ElectricalSets  2  Satelite   3   Satelite Receiver 
4  CottonProducts

I dont know how to use natural join?
with thanks.

_____________
Mehdi.
software student.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-17 : 05:36:23

try this i am not sure is it correct just check once
;WITH Cte( GOODID, FGOODID,description )
AS
(
SELECT
ld.GOODID, ld.FGOODID,convert(varchar(max),ld.type)
FROM
samptab ld

UNION ALL

SELECT
ld.GOODID, ld.FGOODID, ld.type+ ' --> '+ convert(varchar(32),cd.goodid)+' ' + cd.description
FROM
samptab ld
INNER JOIN
cte cd ON cd.FGOODID = ld.GOODID
)

select description from cte where fgoodid IS NULL order by goodid

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-17 : 05:42:17

by using joins but it will works for only 3 child records
select distinct ld1.goodid , ld1.type ,ld.goodid , ld.type , d.goodid,d.type
from urtable d
inner join urtable ld on ld.goodid = d.fgoodid
inner join urtable ld1 on ld1.goodid = ld.fgoodid

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2009-01-17 : 06:50:59

try this which displays ids only, if u want description also change the code accordingly


DROP TABLE #Table

DECLARE @T TABLE (GOODID INT, Type VARCHAR(100), FGOODID INT)

INSERT @T
SELECT 1, 'ElectricalSets', null UNION ALL
SELECT 2, 'Satelite', 1 UNION ALL
SELECT 3, 'Satelite Dish', 2 UNION ALL
SELECT 4, 'CottonProducts', null UNION ALL
SELECT 5, 'Satelite Receiver', 2


; WITH Cte ( GoodId, FGoodId, Parent, Lvl)
AS
(
		SELECT GoodId, FGoodId, GoodId, CAST(1 AS INT)
		FROM @T
		WHERE FGoodId IS NULL
		
		UNION ALL
		
		SELECT T.GoodId, T.FGoodId, C.Parent, C.Lvl + 1
		FROM @T T
		INNER JOIN Cte C ON C.GoodId = T.FGoodId
)

SELECT *, ROW_NUMBER() OVER (PARTITION BY FGoodId, Parent ORDER BY FGoodId, Parent) AS 'RowSeq'
		, MAX(Lvl) OVER (PARTITION BY Parent) AS 'ColSeq'
INTO #Table
FROM Cte

DECLARE @StrCols VARCHAR(MAX),
		@StrFinalCols VARCHAR(MAX),
		@Str VARCHAR(MAX)
		
SELECT	@StrCols = '',
		@StrFinalCols = '',
		@Str = ''
		
SELECT	@StrCols = @StrCols + ', MAX(CASE WHEN Lvl = ' + Lvl + ' THEN GoodId END) AS "GoodId' +  Lvl + '"',
		@StrFinalCols = @StrFinalCols + ',' + CASE WHEN ColSeq = 1 THEN 'GoodId' +  Lvl ELSE
											'CASE WHEN GoodId' + Lvl + ' IS NOT NULL THEN GoodId' +  Lvl + ' 
												ELSE (SELECT GoodId FROM #Table 
														WHERE RowSeq = 1 
																AND Parent = A.Parent 
																AND Lvl = ' + Lvl + ') END AS "GoodId' +  Lvl + '"'
											END
FROM	(
		SELECT DISTINCT CONVERT(VARCHAR(10), Lvl) AS 'Lvl', CASE WHEN Lvl = ColSeq AND ColSeq <> 1 THEN 1 ELSE 0 END as ColSeq
		FROM #Table
		) A

SELECT	@StrFinalCols = STUFF(@StrFinalCols, 1, 1, '')

SELECT @Str = 'SELECT ' + @StrFinalCols +'
				FROM (
						SELECT RowSeq, Parent
						' + @StrCols + '
						FROM #Table
		GROUP BY RowSeq, Parent
		) A'

EXEC(@Str)

"There is only one difference between a dream and an aim.
A dream requires soundless sleep to see,
whereas an aim requires sleepless efforts to achieve..!!"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources