Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL - Parent/Child Query

Posted on 2010-09-05
12
Medium Priority
?
412 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
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

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 2000 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: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Recursive SQL is one of the most fascinating and powerful and yet dangerous feature offered in many modern databases today using a Common Table Expression (CTE) first introduced in the ANSI SQL 99 standard. The first implementations of CTE began ap…
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, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

885 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