Tree stucture

Question

Tree stucture

karunakar2351

Right there with Babe

Points: 729
More actions
January 11, 2016 at 4:19 am

#310673

Hi,
declare @test-2 table (ParentId varchar(10), ChildId varchar(10))
declare @test1 table (ParentId varchar(10), ChildId varchar(10))
insert into @test-2 (ParentId, ChildId)
select 'a', null
union all
select 'a', null
union all
select 'b', null
union all
select 'c', null
union all
select 'd', null
insert into @test1 (ParentId, ChildId)
select 'a', 11
union all
select 'b', 24
union all
select 'a', 13
union all
select 'b', 21
union all
select 'c', 22
select * from @test-2
select * from @test1
i need the output like
coumnname
a
11
13
b
24
21
c
22
d

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

It looks like a client formatting thing: I wouldn't bother doing that in T-SQL. Is there a particular reason why you need that?

-- Gianluca Sartori

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 2

Generally, MS SQL has recursive CTE for trees processing. This particular tree has exactly 2 levels which allows for simple solution.

declare @Test table (ParentId varchar(10), ChildId varchar(10));

declare @Test1 table (ParentId varchar(10), ChildId varchar(10));

insert into @Test (ParentId, ChildId)

select 'a', null

union all

select 'a', null

union all

select 'b', null

union all

select 'c', null

union all

select 'd', null;

insert into @Test1 (ParentId, ChildId)

select 'a', 11

union all

select 'b', 24

union all

select 'a', 13

union all

select 'b', 21

union all

select 'c', 22;

select res = case rowtype when 0 then ParentId else ChildId end

from(

select rowtype = 0, ParentId, ChildId from @test

union -- all

select rowtype = 1, ParentId, ChildId from @test1

) x

order by ParentId,rowtype,ChildId;

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 3

karunakar2351 (1/11/2016)
Hi,
declare @test-2 table (ParentId varchar(10), ChildId varchar(10))
declare @test1 table (ParentId varchar(10), ChildId varchar(10))
insert into @test-2 (ParentId, ChildId)
select 'a', null
union all
select 'a', null
union all
select 'b', null
union all
select 'c', null
union all
select 'd', null
insert into @test1 (ParentId, ChildId)
select 'a', 11
union all
select 'b', 24
union all
select 'a', 13
union all
select 'b', 21
union all
select 'c', 22
select * from @test-2
select * from @test1
i need the output like
coumnname
a
11
13
b
24
21
c
22
d

First, thank you for the readily consumable data. Well done!

Lets hope the "tree" (which this ISN'T) has only the two levels given. If it does, then the following will work.

--===== Create the test data.

-- This is NOT a part of the solution

declare @Test1 table (ParentId varchar(10), ChildId varchar(10))

;

insert into @Test1 (ParentId, ChildId)

select 'a', 11

union all

select 'b', 24

union all

select 'a', 13

union all

select 'b', 21

union all

select 'c', 22

;

--===== A different solution using GROUP BY and GROUPING,

-- which only uses a single pass on one table.

SELECT SomeColumnName = CASE

WHEN GROUPING(ChildID) = 0 THEN ChildID

ELSE ParentID

END

FROM @Test1

GROUP BY ParentId, ChildId WITH ROLLUP

HAVING GROUPING(ParentID) = 0

ORDER BY ParentID, GROUPING(ChildID) DESC, ChildID

;

That produces the following, as requested...

SomeColumnName

--------------

a

11

13

b

21

24

c

22

(8 row(s) affected)

Now, as Gianluca suggested, this type of formatting is normally accomplished by the front end. Remembering that this is a two way street and we can learn, as well, why do you need this done in T-SQL?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 4

Jeff Moden (1/11/2016)
That produces the following, as requested...
SomeColumnName
--------------
a
11
13
b
21
24
c
22
(8 row(s) affected)

d is missing. Just in this case both tables need to be taken into account.

And i should say first solution implies @test1 is subject to FK to @test-2 or that solution will fail too.

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 5

Ah yes. Thanks for the catch on my miss. And, I agree. We're assuming the proper FK's are in place.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

I'm still interested in why this needs to be done, especially in T-SQL but it would appear the OP has flown the coop.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)