Select Query help - 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 2005 Forums

Transact-SQL (2005)

Select Query help

Author

Topic

cruxmagi
Starting Member

38 Posts

Posted - 2009-02-19 : 04:05:06

I have a table which has the following structure
CmtId ParentId Level Cmt
359 358 1 MTB
360 NULL 0 no gravity
361 360 1 no gravity
362 NULL 0 wethepeople beyond
363 NULL 0 wethepeople beyond
364 363 1 wethepeople beyond
365 NULL 0 wethepeople beyond
366 364 2 wethepeople beyond
367 366 3 wethepeople beyond

i want the result as like as follows
CmtId ParentId Level Cmt
359 358 1 MTB
360 NULL 0 no gravity
361 360 1 no gravity
362 NULL 0 wethepeople beyond
363 NULL 0 wethepeople beyond
364 363 1 wethepeople beyond
366 364 2 wethepeople beyond
367 366 3 wethepeople beyond
365 NULL 0 wethepeople beyond
could you help me out

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-02-19 : 04:20:24

select * from @tab order by case when cmt='wethepeople beyond' then Level end

cruxmagi
Starting Member

38 Posts

Posted - 2009-02-19 : 04:24:41

Sorry

cmt='wethepeople beyond' is a dummy text,it might not be repeated,to give a structure i just filled it.

quote:
Originally posted by bklr

select * from @tab order by case when cmt='wethepeople beyond' then Level end

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-02-19 : 05:04:47

Can you explain what output you need ?

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 05:12:48

Try this Once,



declare @temp table (CmtId INT, ParentId INT,Level varchar(32),Cmt VARCHAR(32))
insert into @temp
select 359, 358, 1, 'MTB' UNION all
select 360 ,NULL, 0, 'no gravity' UNION all
select 361, 360, 1, 'no gravity' UNION all
select 362,NULL, 0, 'wethepeople beyond' UNION all
select 363, NULL, 0, 'wethepeople beyond' UNION all
select 364, 363, 1 ,'wethepeople beyond' UNION all
select 365, NULL, 0, 'wethepeople beyond' UNION all
select 366, 364 ,2, 'wethepeople beyond' UNION all
select 367, 366 ,3, 'wethepeople beyond'

;WITH cte (CmtId, ParentId, Level, Cmt, path,Indent)
AS (
	SELECT	CmtId,
		ParentID,
		level,
		Cmt,
		CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY cmt)),
		0
	FROM	@temp
	WHERE	ParentID IS NULL

	UNION ALL

	SELECT	s.CmtId,
			s.ParentID,
			s.level,
			s.Cmt,
			CONVERT(VARCHAR, y.Path + ',' + CONVERT(VARCHAR, ROW_NUMBER() OVER (ORDER BY s.cmt DESC))),
			y.Indent + 1
	FROM		@temp AS s
	INNER JOIN	cte AS y ON y.CmtId = s.ParentID
)

SELECT		CmtId,
		ParentID,
		REPLICATE('', Indent) + cmt,
		level
FROM		cte
ORDER BY	Path

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-02-19 : 06:12:56

declare @temp table (CmtId INT, ParentId INT,Level varchar(32),Cmt VARCHAR(32))
insert into @temp
select 359, 358, 1, 'MTB' UNION all
select 360 ,NULL, 0, 'no gravity' UNION all
select 361, 360, 1, 'no gravity' UNION all
select 362,NULL, 0, 'wethepeople beyond' UNION all
select 363, NULL, 0, 'wethepeople beyond' UNION all
select 364, 363, 1 ,'wethepeople beyond' UNION all
select 365, NULL, 0, 'wethepeople beyond' UNION all
select 366, 364 ,2, 'wethepeople beyond' UNION all
select 367, 366 ,3, 'wethepeople beyond'

;with cte ( cmtid,parentid,level,cmt,path ) as
(
select *,convert(varchar(max),cmtid) as path from @temp where parentid is null
union all
select t.cmtid,t.parentid,t.level,t.cmt,c.path+'-'+ convert(varchar(32),t.cmtid) from @temp t
inner join cte c on c.cmtid = t.parentid
)
select c1.cmtid,c1.parentid,c1.level,c1.cmt from @temp c1
left join cte t1 on t1.cmtid = c1.cmtid
order by path

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources