Solved

Fetching Categories from SQLSERVER

Posted on 2014-03-11
19
292 Views
Last Modified: 2014-04-02
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.
0
Comment
Question by:xeondxb
19 Comments
 
LVL 12

Accepted Solution

by:
Harish Varghese earned 500 total points
ID: 39919900
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
 
LVL 11

Expert Comment

by:John_Vidmar
ID: 39920379
select	[Parent]	=	p.name
,	[Child]		=	c.name
from	Categories	p
join	Categories	c	on	p.parent = c.id

Open in new window

0
 

Author Comment

by:xeondxb
ID: 39920598
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
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39921183
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
 

Author Comment

by:xeondxb
ID: 39921455
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
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39921493
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
 

Author Comment

by:xeondxb
ID: 39921523
:) 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
 
LVL 52

Expert Comment

by:_agx_
ID: 39923719
(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
 

Author Comment

by:xeondxb
ID: 39926047
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Closing Comment

by:xeondxb
ID: 39926061
it is Working :) ..... you should be on Top in Experts List....
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39926103
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
 

Author Comment

by:xeondxb
ID: 39932195
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
 

Author Comment

by:xeondxb
ID: 39932200
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
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39933561
Welcome :)
0
 

Author Comment

by:xeondxb
ID: 39971365
hi Harish,

can i ask you some thing regarding same query ?
if you need to open new question i have no problem ??
0
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39971494
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
 

Author Comment

by:xeondxb
ID: 39971777
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
 
LVL 12

Expert Comment

by:Harish Varghese
ID: 39972029
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
 

Author Comment

by:xeondxb
ID: 39974069
thank you very much!!!!!
Really Appreciated, I hope it will help others also
thanks again...  @Harish
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now