CODEWITHSUNDEEP
sqlsql-server
user3656218
user3656218

Reputation: 23

SQL Server : How to get same "parent" value for all children

In my table I have 3 columns like below:

No.        | Type   |   Amount
-------------------------------
110200014  |    A   | 19,259.00
110200014  |    D   | -802.46
110200014  |    D   | -1,604.92

Type A is for parent record, type D is for children record.

As a result in my request I would like all lines to show "19,259.00" in "amount" column. What is important for me is to get that "parent" value for all children

Upvotes: 0

Views: 83

Answers (2)

Anthony Horne
Anthony Horne

Reputation: 2522

;
WITH cte
AS (
    SELECT *
    FROM TABLE
    WHERE type = 'A'
    )
SELECT cte.Amount, Table.*
FROM TABLE
INNER JOIN cte
    ON TABLE.No = cte.no
WHERE TABLE.type = 'D'

Using the data generated by @ScubaManDan

The following works:

create table #tempTab(Num int,rtype char(1),amount decimal(8,2));

insert into #tempTab values(110200014,'A',19259.00)
insert into #tempTab values(110200014,'D',-802.46)
insert into #tempTab values(110200014,'D',-1604.92)

;WITH cte
AS (
    SELECT *
    FROM #tempTab
    WHERE rtype = 'A'
    )
SELECT cte.Amount, #tempTab.*
FROM #tempTab
INNER JOIN cte
    ON #tempTab.Num = cte.Num
WHERE #tempTab.rtype = 'D'

Produces:

Amount  Num rtype   amount
------------------------------------
19259.00    110200014   D   -802.46
19259.00    110200014   D   -1604.92

Upvotes: 1

ScubaManDan
ScubaManDan

Reputation: 839

How about something like this:

create table #tempTab(Num int,rtype char(1),amount decimal(8,2));

insert into #tempTab values
(110200014,'A',19259.00),
(110200014,'D',-802.46),
(110200014,'D',-1604.92)

select * 
from #tempTab t
cross apply (select amount from #tempTab where rtype = 'A' and Num = t.Num) pa;

Upvotes: 1

Related Questions