Solved

SQL - Parent/Child Query

Posted on 2010-09-05
12
396 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
 
LVL 7

Expert Comment

by:rashmi_vaghela
ID: 33608800
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
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.

734 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