procedure to get data from a child column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

procedure to get data from a child column

Author

Topic

asad.lte
Starting Member

32 Posts

Posted - 2010-04-26 : 03:34:56

hi all,

i am new to this sql server and i need a procedure to get data from a child related fee which is in the 3rd row or in the 5th row. i want that data in the respective parent row.

for example: i have ID,Prod_code,parentid,fee1,fee2,fee3 columns

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-26 : 03:40:51

can you post some sample data and the expected result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 03:51:34

have a look at recursive CTEs in books online

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asad.lte
Starting Member

32 Posts

Posted - 2010-04-26 : 05:15:44

hi ,

id prod_code parentid fee1 fee2 fee3
100 aaa noparent 50 0 0
100 bbb aaa 20 0 0
100 ccc aaa 30 0 0 data is presnt in this fashion.i need output as

id prod_code parentid fee1 fee2 fee3
100 aaa noparent 50 20 30.
there are thousands of records in this fashion u got my point right plz help me in this thnx in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-26 : 05:54:33

quote:
Originally posted by asad.lte

hi ,

id prod_code parentid fee1 fee2 fee3
100 aaa noparent 50 0 0
100 bbb aaa 20 0 0
100 ccc aaa 30 0 0 data is presnt in this fashion.i need output as

id prod_code parentid fee1 fee2 fee3
100 aaa noparent 50 20 30.
there are thousands of records in this fashion u got my point right plz help me in this thnx in advance

You only want to show fee1 column of child on the same row as parent ?

How do you determine 20 is fee2 and not fee3 ? And 30 is fee3 and not fee2 ?

fee2 and fee3 for parent or child will not contain any value ? Any logic on these 2 column ?

Will there be more than 1 level of child record ?

Are you using SQL 2000 or 2005 / 2008 ?

KH
[spoiler]Time is always against us[/spoiler]

asad.lte
Starting Member

32 Posts

Posted - 2010-04-26 : 06:08:37

hi ,

m using sql server 2008,for ur info we have two categories of products.one is product having noparent (individual product) and the other is product with parents (childs).wat v r expecting is we hav data for a parent in the parent row and we have their respective childs fee in the different row .we want that child column fee as fee2 and in tht manner.Just one level of relation is their ,hope u got my point

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-26 : 06:22:13

[code]
declare @sample table
(
id int,
prod_code varchar(3),
parentid varchar(8),
fee1 int,
fee2 int,
fee3 int
)
insert into @sample
select 100, 'aaa', 'noparent', 50, 0, 0 union all
select 100, 'bbb', 'aaa', 20, 0, 0 union all
select 100, 'ccc', 'aaa', 30, 0, 0

-- the QUERY
; with
cte
as
(
select id, prod_code, parentid, fee1, fee2, fee3,
child_id = row_number() over (partition by id
order by case when parentid = 'noparent' then 1 else 2 end, parentid)
from @sample
)
select id,
prod_code = max(case when child_id = 1 then prod_code end),
parentid = max(case when child_id = 1 then parentid end),
free1 = sum(case when child_id = 1 then fee1 end),
free2 = sum(case when child_id = 2 then fee1 end),
free3 = sum(case when child_id = 3 then fee1 end)
from cte
group by id

/*
id prod_code parentid free1 free2 free3
----------- --------- -------- ----------- ----------- -----------
100 aaa noparent 50 20 30

(1 row(s) affected)
*/
[/code]

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 06:47:16

[code]SELECT t.id,t.prod_code,t.parentid,t.fee1,
MAX(CASE WHEN t1.rn=1 THEN t1.fee1 ELSE NULL END) AS fee2,
MAX(CASE WHEN t1.rn=2 THEN t1.fee1 ELSE NULL END) AS fee3
FROM Table t
CROSS APPLY (SELECT TOP 2 ROW_NUMBER() OVER (ORDER BY prod_code) AS rn,fee1
FROM Table
WHERE parentid = t.prod_code
ORDER BY prod_code
)t1
WHERE t.parentid = 'noparent'
GROUP BY t.id,t.prod_code,t.parentid,t.fee1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asad.lte
Starting Member

32 Posts

Posted - 2010-04-26 : 08:12:43

hi ,

thnx first of all for ur query it helped me much but i hav one more, wid this requirement i need to add more fee columns fee4,fee5,fee5 to the parent row for the future purpose wid no data.we dont hav data as of now .plz help me

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-26 : 08:32:58

[code]
-- the QUERY
; with
cte
as
(
select id, prod_code, parentid, fee1, fee2, fee3,
child_id = row_number() over (partition by id
order by case when parentid = 'noparent' then 1 else 2 end, parentid)
from @sample
)
select id,
prod_code = max(case when child_id = 1 then prod_code end),
parentid = max(case when child_id = 1 then parentid end),
free1 = sum(case when child_id = 1 then fee1 end),
free2 = sum(case when child_id = 2 then fee1 end),
free3 = sum(case when child_id = 3 then fee1 end),
free4 = sum(case when child_id = 4 then fee1 end),
free5 = sum(case when child_id = 5 then fee1 end)
from cte
group by id
[/code]

KH
[spoiler]Time is always against us[/spoiler]

asad.lte
Starting Member

32 Posts

Posted - 2010-04-26 : 09:51:11

Hi Kh,

ur query is giving the proper output with respect to just 'noparent'(individual product ) with this i need products with parents aswell m i confusing you no right ,plz don mind with my queries .help me m stuck in the middle.ur query is executing properly itz my mistake i forgot to post tht requirement

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-26 : 23:33:43

what about this small change?


SELECT t.id,t.prod_code,t.parentid,t.fee1,
MAX(CASE WHEN t1.rn=1 THEN t1.fee1 ELSE NULL END) AS fee2,
MAX(CASE WHEN t1.rn=2 THEN t1.fee1 ELSE NULL END) AS fee3,
MAX(CASE WHEN t1.rn=2 THEN t1.fee1 ELSE NULL END) AS fee4,
MAX(CASE WHEN t1.rn=2 THEN t1.fee1 ELSE NULL END) AS fee5
FROM Table t
CROSS APPLY (SELECT TOP 4 ROW_NUMBER() OVER (ORDER BY prod_code) AS rn,fee1
             FROM Table
             WHERE parentid = t.prod_code
             ORDER BY prod_code
             )t1
GROUP BY t.id,t.prod_code,t.parentid,t.fee1

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asad.lte
Starting Member

32 Posts

Posted - 2010-04-27 : 02:43:16

hi visakh,
thnx for ur support .but m getting amount only in fee1 columns but whr as my fee2 fee3 is showing '0' i think tht is da exact output .plz help me to fix this

asad.lte
Starting Member

32 Posts

Posted - 2010-04-27 : 02:57:03

visakh,

hav u seen my todays post .m not getting da proper output i think.plz help me to fix this .i need individual products and products with parents both

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-27 : 02:59:22

quote:
Originally posted by asad.lte

Hi Kh,

ur query is giving the proper output with respect to just 'noparent'(individual product ) with this i need products with parents aswell m i confusing you no right ,plz don mind with my queries .help me m stuck in the middle.ur query is executing properly itz my mistake i forgot to post tht requirement

Don't quite understand what do you mean here . . does your original sample data illustrate this ?

KH
[spoiler]Time is always against us[/spoiler]

asad.lte
Starting Member

32 Posts

Posted - 2010-04-27 : 03:12:52

hi guys,

sory to trouble u on da same issue.wat i meant was i hav two things in my table prod_code with noparent (individual product) and prod_code with parents (bundle) .i want the output for individual products and bundle products.hope u got my point .this table is respect to subscriptions for a particular product.for some customers v hav one subscription or 2,3,... something like tht.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-27 : 03:18:40

it will be easier if you can post some sample data to show this. Also include the expected result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 03:49:25

quote:
Originally posted by asad.lte

hi guys,

sory to trouble u on da same issue.wat i meant was i hav two things in my table prod_code with noparent (individual product) and prod_code with parents (bundle) .i want the output for individual products and bundle products.hope u got my point .this table is respect to subscriptions for a particular product.for some customers v hav one subscription or 2,3,... something like tht.

show how data will be for your two cases

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

asad.lte
Starting Member

32 Posts

Posted - 2010-04-27 : 03:53:41

this is my table
ID,prod_code,tran,parentid,freq,tname,fee1,fee2,fee3,paname
100000 llnk LLSA NoParent M Monthly Subscription 0 0 0 NoParent DeskLink#153 NULL NULL
100000 llwz LWSA NoParent M Monthly Subscription 0 0 0 NoParent FinanceWizard#153 NULL NULL
100022 cmpl CMSA b003 M Monthly Subscription 0 0 0 Compliance DealWatch#153 NULL 0

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-04-27 : 03:58:45

Also include the expected result

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-27 : 04:01:47

the third record has parentid as b003 which doesnt seem to be valid value in prod_code. then on waht basis you find the children of a record?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources