August 17, 2011 at 4:51 am
create table #t1(id int,name varchar(20))
create table #t1details(did int,id int,num varchar(10))
insert into #t1details(id,name)
select 1,'abc'
union
select 2,'bcd'
union
select 3,'efg'
insert into #t1details(did,id,num)
select 1,1,'101'
union
select 2,1,'102'
union
select 3,1,'103'
union
select 4,2,'101'
union
select 5,2,'102'
union
select 6,2,'103'
i have a table and i want my parent id to be displayed only onece
and rest to its related rows in another table it should be
displayed as null. it will be like a tree structure
i want output to look like:
a.id a.name b.num
1 abc 101
null null 102
null null 103
2 bcd 101
null null 102
I want the parent to be displayed only once and from next row it should show
null but the child in #t1details table whould be displayed.
August 17, 2011 at 5:17 am
Please next time, test your code before posting so its runs without error!
Also, you've created another thread for the same question!
You have very strange requirements, but I leave it with you...
The following code will do it exactly what you've asked for:
;with cte
as
(
select t.id, t.name, td.did, td.num,
ROW_NUMBER() OVER (PARTITION BY t.id ORDER BY td.did) rn,
ROW_NUMBER() OVER (ORDER BY t.id, td.did) ro
from #t1 t
join #t1details td on td.id = t.id
)
select case when rn = 1 then id else null end as id
,case when rn = 1 then name else null end as name
,num
from cte
order by ro
August 17, 2011 at 5:35 am
Thank a lot .
August 17, 2011 at 6:00 am
Happy to help, but I wonder what scotsman has to do with RosAviaKosmos ? 😀
August 17, 2011 at 12:42 pm
But i have one more..
the data shows to final column
101
102
103
but i tried to change some part but i m not getting same data but with one extra row which will be like summary row with max value of the final column on ID(the first column)
eg.
ID Name Num
-----------------
1 abc 103
null null 103
null null 102
null null 101
2 bcd 102
null null 102
null null 101
....
as u can see the first row is max value on ID and next row will follow the query you gave.
I tried to use group by on CTE query but its is using extra columns which is not giving desired results.
I tried to use Roll up and Cube but not getting proper result.
So wt i have to change in your query.
Scottich Via Kosmos 🙂
August 18, 2011 at 3:18 am
You are lucky man than J. Celko didn't see you last request, otherwise you would be asked to read his books and learn that you should not do this (mixing sums and details).
However, in my experience I was need to do it quite often. Here is the way to achieve it:
select id, name, num
from
(
select 1 as odr, t.id as cid, t.id, t.name, s.mnum as num
from #t1 t
join (select id, MAX(num) mnum from #t1details group by id) s
on s.id = t.id
union all
select 2 as odr, id as cid, null, null, num
from #t1details
) q
order by cid, odr, num desc
You can create mulitple "odering" columns which would allow to combine even more uncombinable data.:-D
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply