Fetching Categories from SQLSERVER

hi Guys,

I have a Categories Table.

ID    -    NAME   -  Parent
----------------------------------

1    -     Cat_1     -      0
2    -     Cat_2    -       0
3   -      Cat_2    -       0
4   -      SCat_1  -        1
5   -      SCat_3  -        3
6   -      SCat_2  -        2
7   -      SCat_4  -        4
8   -      SCat_4  -        4

Is there anyway I can get result in one Query

for example :

* Cat_1(Parent)

  - SCat_1
      - SCat_4
      - SCat_4

* Cat_2  (Parent)
  - SCat_2

* Cat_3 (Parent)
  - SCat_3

Thanks.
xeondxbAsked:
Who is Participating?
 
Harish VargheseProject LeaderCommented:
Hello,

You may use recursive CTE for this:
create table Categories
(ID int primary key, name varchar(20), Parent int)
go


with CTE as (
	Select '* ' as Symbol, 0 as Level, 
	convert(varchar(100), Right('0000' + convert (varchar, ROW_NUMBER() over (order by id)), 5)) SortKey, * from Categories 
	Where Parent = 0
	Union All
	Select '- ' as Sysmbol, P.Level + 1 as Level, 
	convert(varchar(100),P.SortKey + '-' + Right('0000' + convert (varchar(100), ROW_NUMBER() over (order by p.id, c.id)),5)), C.* from Categories C, CTE P
	Where C.Parent = P.ID 
)
select Replicate(' ', Level * 3) + Symbol + Name, * from CTE
Order by SortKey 

Open in new window

-Harish
0
 
John_VidmarCommented:
select	[Parent]	=	p.name
,	[Child]		=	c.name
from	Categories	p
join	Categories	c	on	p.parent = c.id

Open in new window

0
 
xeondxbAuthor Commented:
Hi Guys,

Thanks for help.

*Harish. just giving you table script please have a look thanks


USE [contacts]
GO

/****** Object:  Table [dbo].[Tbl_Category]    Script Date: 3/11/2014 6:15:36 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Tbl_Category](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [varchar](50) NOT NULL,
	[Parent] [int] NOT NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Tbl_Category] ADD  CONSTRAINT [DF_Tbl_Categories_Parent]  DEFAULT ((0)) FOR [Parent]
GO

Open in new window

DB.jpg
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
Harish VargheseProject LeaderCommented:
The structure is same. You can just replace the tablename - Categories with Tbl_Categories.
;with CTE as (
	Select '* ' as Symbol, 0 as Level, 
	convert(varchar(100), Right('0000' + convert (varchar, ROW_NUMBER() over (order by id)), 5)) SortKey, * from Tbl_Categories 
	Where Parent = 0
	Union All
	Select '- ' as Sysmbol, P.Level + 1 as Level, 
	convert(varchar(100),P.SortKey + '-' + Right('0000' + convert (varchar(100), ROW_NUMBER() over (order by p.id, c.id)),5)), C.* from Tbl_Categories C, CTE P
	Where C.Parent = P.ID 
)
select Replicate(' ', Level * 3) + Symbol + Name from CTE
Order by SortKey 

Open in new window

-Harish
0
 
xeondxbAuthor Commented:
Thanks Harish,

it is working but is it for 1st level only ?? if I don't know how many levels are there what change I should do ??
0
 
Harish VargheseProject LeaderCommented:
You dont have to worry about the number of levels. It is recursive and the default limit is 100 levels. Hope you will not have so many levels.

-Harish
0
 
xeondxbAuthor Commented:
:) no off course not im not going to use 100 levels, But  im not getting desired results.
 please have a look attached screen shoots.

Thanks
Result.jpg
select-All-records.jpg
0
 
_agx_Commented:
(no points...)

@xeondxb - Without seeing the raw query dump, it is hard to tell if the problem is the SQL query, the sorting OR  the output code. Can you post a cfdump of the raw query Harish Varghese recommended?
0
 
xeondxbAuthor Commented:
hi Harish,

it is working fine now could you tell me how I can get all the columns? right now it is giving only name ...


and one more question

can I change 3rd level symbol from "-"  to something else ....

Thanks man you are guru :)
0
 
xeondxbAuthor Commented:
it is Working :) ..... you should be on Top in Experts List....
0
 
Harish VargheseProject LeaderCommented:
Thanks. To get all column names (or other columns you need), you can add * or the column names in the final SELECT statement, as below:

SELECT Replicate(' ', Level * 3) + Symbol + Name, * /*Column Names here */ from CTE
Order by SortKey

And below is a generic code to change the symbols for different levels:
Declare @LevelSymbols varchar(200)
SET @LevelSymbols = '*-=' + REPLICATE('#',100)
;with CTE as (
	Select 0 as Level, 
	convert(varchar(100), Right('0000' + convert (varchar, ROW_NUMBER() over (order by id)), 5)) SortKey, * from Tbl_Categories 
	Where Parent = 0
	Union All
	Select P.Level + 1 as Level, 
	convert(varchar(100),P.SortKey + '-' + Right('0000' + convert (varchar(100), ROW_NUMBER() over (order by p.id, c.id)),5)), C.* from Tbl_Categories C, CTE P
	Where C.Parent = P.ID 
)
select Replicate(' ', Level * 3) + SUBSTRING(@LevelSymbols, Level+1, 1) + Name, * /*Column Names Here*/ from CTE
Order by SortKey 

Open in new window

Note the second line in the code:

SET @LevelSymbols = '*-=' + REPLICATE('#',100)

We are mentioning a sequence of symbols in order, first symbol will be used for Level 1, second for Level 2, and so on. And if you want all remaining levels to use same symbol, that can be mentioned inside the REPLICATE clause (I have mentioned # in the example). So in our code, first level will show *, second -, third = and fourth onwards #.

-Harish
0
 
xeondxbAuthor Commented:
hi Harish,

Thanks for your support,

Thanks for your last script also only one problem im facing in last script what you sent me, Please have a look screen shoot im getting results 2 times and my ColdFusion application fetching normal query without indented.


 

Declare @LevelSymbols varchar(200)
SET @LevelSymbols = '*-=' + REPLICATE('#',100)
;with CTE as (
	Select 0 as Level, 
	convert(varchar(100), Right('0000' + convert (varchar, ROW_NUMBER() over (order by id)), 5)) SortKey, * from Tbl_Category 
	Where Parent = 0
	Union All
	Select P.Level + 1 as Level, 
	convert(varchar(100),P.SortKey + '-' + Right('0000' + convert (varchar(100), ROW_NUMBER() over (order by p.id, c.id)),5)), C.* from Tbl_Category C, CTE P
	Where C.Parent = P.ID 
)
select Replicate(' ', Level * 3) + SUBSTRING(@LevelSymbols, Level+1, 1) + Name,id,Parent, * /*Column Names Here*/ from CTE
Order by SortKey 

Open in new window

DB.jpg
0
 
xeondxbAuthor Commented:
Found the problem ! there was no problem :)


Line Number 12: I had to assign columns names

select Replicate(' ', Level * 3) + SUBSTRING(@LevelSymbols, Level+1, 1) + Name[Name],id[ID],Parent[Parent], * /*Column Names Here*/ from CTE
Order by SortKey 

Open in new window


Thanks Harish.....
0
 
Harish VargheseProject LeaderCommented:
Welcome :)
0
 
xeondxbAuthor Commented:
hi Harish,

can i ask you some thing regarding same query ?
if you need to open new question i have no problem ??
0
 
Harish VargheseProject LeaderCommented:
No problem. You can ask here. But in case you dont see any response from me or anyone else, you may create a new question.
0
 
xeondxbAuthor Commented:
thank you!!! @Harish

can you please explain what is happening in your script ... My T-SQL not  good but after see your script i started reading t-sql :).

It will help me what is happening ...
You are genius --- :)
0
 
Harish VargheseProject LeaderCommented:
Hello,

Thanks for the compliment. There are so many experts people here and I too learn a lot from here.
What we have used is called Resursive CTE (Common Table Expression). Here is a small introduction about CTE. A CTE has two parts - Anchor and Recursion. The anchor defines the first level rows of the CTE. Below is the anchor sql in our case:
Select 0 as Level, 
	convert(varchar(100), Right('0000' + convert (varchar, ROW_NUMBER() over (order by id)), 5)) SortKey, * from Tbl_Categories 
Where Parent = 0

Open in new window

The second sql after UNION ALL is the recursive member which acts on the result of the anchor query. The recursive query will be executed repeatedly until no more rows are returned.
You may find more details on CTE on the web. Here is a link:
http://technet.microsoft.com/en-us/library/ms186243(v=sql.105).aspx

And about the logic we used.. We are generating a sequence of strings (sort keys) for sorting purpose. The anchor generates strings like 00001, 00002, 00003, etc. as sort keys.
The recursive sql will concatenate similar strings to the sort key returned from anchor. So the second level of items for first item will get 00001-00001, 00001-00002, etc. as sort keys. Similarly, the second level of items for second item will get sort keys as 00002-00001, 00002-00002, etc.
When the recursive sql will be executed the next time (third level), the sort keys generated will be 00001-00001-00001, 00001-00001-00002, etc. And each level will append a new 5 character code to the sort key. This will make sure the order we want when sorted on the sort key.
The ROW_NUMBER() function generates serial numbers for the result set returned by the sql.
I hope you got some idea.

-Harish
0
 
xeondxbAuthor Commented:
thank you very much!!!!!
Really Appreciated, I hope it will help others also
thanks again...  @Harish
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.