Solved

SQL - Parent/Child Query

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

Expert Comment

by:rashmi_vaghela
ID: 33608800
0
 

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

Join & Write a Comment

Suggested Solutions

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 …
If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

760 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

22 Experts available now in Live!

Get 1:1 Help Now