Solved

SQL - Parent/Child Query

Posted on 2010-09-05
12
401 Views
Last Modified: 2012-05-10
Dear experts,

I have got the attached SQL Table, and I need to have a query that outputs the columns in yellow from that table (groupLevel1, groupLevel2, etc…)
The idea is to have each column of these display the category name for all following ancestors, until it reaches the last ancestor. This means the columns are not going to be only four. If there are 10 levels, there will be groupLevel10!

Hope that is clear!
Thank you in advance,
   
ee-parentchild.png
0
Comment
Question by:feesu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 1

Expert Comment

by:z_alex
ID: 33606379
In that case you can use the sql tree.
Try to check the link below
http://www.codeproject.com/KB/database/Trees_in_SQL_databases.aspx
0
 

Author Comment

by:feesu
ID: 33606440
I don't think I need that complexity. I only want my query to return these extra columns based on the criteria above!
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Comment

by:feesu
ID: 33609454
rashmi,

I still don't know how to create those dynamic columns I mentioned above in the query!

Thanks,
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33613933
feesu, can you please rephrase your question? Also please post: (1) some sample data of your current table and (2) what is the expected result (what you want from the query) It is important that you post both the input and the out along with the rationale in order for us to give you an accurate resposne.
 
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33614012
In the meantime, you will find it easier to do it this way (in just one column). See example below along with resultset:
declare @hier table (
	id int,
	parentid int,
	category varchar(20),
	[order] int,
	level int
)

insert @hier values(1, 5, 'rent', 1, 3)
insert @hier values(2, 1, 'House Rent', 1, 4)
insert @hier values(3, 1, 'Office Rent', 2, 4)
insert @hier values(4, 1, 'Car Rent', 3, 4)
insert @hier values(5, 9, 'Revenue', 1, 2)
insert @hier values(6, 9, 'Expenses', 2, 2)
insert @hier values(7, 6, 'Salaries', 1, 3)
insert @hier values(8, 6, 'Others', 2, 3)
insert @hier values(9, null, 'Operations', 1, 1)
insert @hier values(10, null, 'Investment', 2, 1)


select * from @hier

;with CTE as (
	select id, parentid, category, [order], level, cast(category as varchar(max)) as tree from @hier
	where parentid is null
	union all
	select b.id, b.parentid, b.category, b.[order], b.level, a.tree + '\' + b.category
	from CTE a
	inner join @hier b on b.parentid = a.id
)
select * from CTE
order by id
option(maxrecursion 0)

-And here is the result
id          parentid    category             order       level       tree
----------- ----------- -------------------- ----------- ----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           5           rent                 1           3           Operations\Revenue\rent
2           1           House Rent           1           4           Operations\Revenue\rent\House Rent
3           1           Office Rent          2           4           Operations\Revenue\rent\Office Rent
4           1           Car Rent             3           4           Operations\Revenue\rent\Car Rent
5           9           Revenue              1           2           Operations\Revenue
6           9           Expenses             2           2           Operations\Expenses
7           6           Salaries             1           3           Operations\Expenses\Salaries
8           6           Others               2           3           Operations\Expenses\Others
9           NULL        Operations           1           1           Operations
10          NULL        Investment           2           1           Investment

(10 row(s) affected)

Open in new window

0
 

Author Comment

by:feesu
ID: 33615669
Hi ralmada,

My table structure is as the snapshot above without the output columns in yellow. So it is (ID, ParentID, Category, Order, Level).

The data is same as the data I have in my table.

And I want using that structure and data a select statement that generates those extra columns in yellow (GroupHeader_1,2,3 etc...)

Thanks!
0
 
LVL 41

Expert Comment

by:ralmada
ID: 33618608
In my opinion, my suggestion above will be your best bet to get that information.
If you still want separate columns instead of just one, then you will have to do something more complex, so:
 
 

----------------------------------------------------
1) Create the parmstolist function below:
----------------------------------------------------

create FUNCTION [dbo].[ParmsToList] (@Parameters varchar(8000), @delimiter varchar(10) ) 
returns @result TABLE (Value varchar(500), rn int identity) 
AS 
begin 
declare @dx varchar(9) 
-- declare @loops int 
--set @loops = 0 

DECLARE @TempList table 
( 
Value varchar(500) 
) 

if @delimiter is null set @delimiter = ' ' 
if len(@delimiter) < 1 set @delimiter = ' ' 
set @dx = left(@delimiter, case when @delimiter = ' ' then 1 else len(@delimiter) end -1) 

DECLARE @Value varchar(8000), @Pos int 

SET @Parameters = LTRIM(RTRIM(@Parameters))+ @delimiter 
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 

IF REPLACE(@Parameters, @delimiter, @dx) <> '' 
BEGIN 
WHILE @Pos > 0 -- AND @Loops < 100 
BEGIN 
--set @loops = @loops + 1 
SET @Value = LTRIM(RTRIM(LEFT(@Parameters, @Pos - 1))) 
IF @Value <> '' 
BEGIN 
INSERT INTO @TempList (Value) VALUES (@Value) --Use Appropriate conversion 
END 
SET @Parameters = SUBSTRING(@Parameters, @Pos+ case when @delimiter = ' ' then 1 else len(@delimiter) end, 8000) 
SET @Pos = CHARINDEX(@delimiter, @Parameters, 1) 

END 
END 
INSERT @result 
SELECT value 
FROM @TempList 
RETURN 
END

----------------------------------------------------
2) Run the follow query against your table to create a stage temp table called #hier2
----------------------------------------------------
;with CTE as (
	select id, parentid, category, [order], level, cast(category as varchar(max)) as tree from yourtable
	where parentid is null
	union all
	select b.id, b.parentid, b.category, b.[order], b.level, a.tree + '\' + b.category
	from CTE a
	inner join yourtable b on b.parentid = a.id
)
select * 
into #hier2
from CTE
order by id
option(maxrecursion 0)


----------------------------------------------------
3) Use the following dynamic SQL with the information from the temp table created in 2
----------------------------------------------------

declare @strSQL varchar(max)
declare @cols varchar(max)

set @cols = stuff((select distinct ', [' + cast(level as varchar) + ']' from yourtable order by 1 for xml path('')), 1, 2, '')



set @strSQL = 'select id, parentid, category, [order], level, ' + @cols + 
	' from (
		select a.id, a.parentid, a.category, a.[order], a.level, b.rn, b.Value
		from #hier2 a
		cross apply dbo.parmstolist(a.tree, '\') b
	) o
	pivot(max(Value) for rn in (' + @cols + ')) as p
	order by id'

exec(@strSQL)

Open in new window

0
 
LVL 1

Expert Comment

by:z_alex
ID: 33625728

select groupLevel1, groupLevel2, groupLevel3, groupLevel4
from TABLENAME
group by groupLevel1, groupLevel2, groupLevel3, groupLevel4
0
 

Author Comment

by:feesu
ID: 33654750
Hi ralmada,

I shall try your code and get back to you. Thank you!
0
 

Author Comment

by:feesu
ID: 33710392
Hi ralmada,

I was able to create the function, but on executing the following query, I got the error below:

Msg 240, Level 16, State 1, Line 1
Types don't match between the anchor and the recursive part in column "tree" of recursive query "CTE".
0
 
LVL 41

Accepted Solution

by:
ralmada earned 500 total points
ID: 33722160
try step 2 again
;with CTE as (
	select id, parentid, category, [order], level, cast(category as varchar(max)) as tree from yourtable
	where parentid is null
	union all
	select b.id, b.parentid, b.category, b.[order], b.level, cast(a.tree + '\' + b.category as varchar(max))
	from CTE a
	inner join yourtable b on b.parentid = a.id
)
select * 
into #hier2
from CTE
order by id
option(maxrecursion 0)

Open in new window

0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

635 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