Linking Column Values on the Same Row

Transact-SQL
1

Hi team,

My understanding of SQL only goes so far and I've had to step into someone else's shoes whilst they are unfortunately off on sick leave. The scenario I'm faced with is trying to build a query that pulls in different contract amounts on per customer.

For example, if I was to pull all contracts for a customer, they would appear row by row like below:

PersonID ContractID Amount
0001 01 100.00
0001 02 200.00
0001 03 250.00

using:

select s.person_id, s.contract_id, c.amount

from db.contract c
JOIN db.subscription s ON ((c.person_id = s.person_id))

group by s.person_id, s.contract_id, c.amount

However, I'm looking at building a view for each customer which has their first contract, previous contract and current contract amounts per customer ID by utilising the first_contract_id and previous_contract_id fields in the db.subscription table. Question is, how do I link these to an amount so that they all appear on one row, like the example below:

PersonID FirstContractAmount PrevContractAmount CurrentContractAmount
0001 100.00 200.00 250.00

Any help you can provide would be hugely appreciated, and I'll be very thankful.

Steve

2

You can try using pivot feature to achieve this.

3

---This is an example on how it works

if object_id('tempdb..#table') is not null drop table #table

create table #table (
person_id int
,contract_id int
,amount float
);

INSERT into #table VALUES
(0001,01,100.00),
(0001,02,200.00),
(0001,03,250.00)

select * from (
select
person_id
,case
when rn = 1 then 'first contract'
when rn = 2 then 'previous contract'
when rn = 3 then 'current contract'
end as contract_type
,amount
from (
select
ROW_NUMBER() over (partition by person_id order by contract_id) rn,
*
from #table
)Fox
)ELma
pivot (sum(amount) for contract_type in (
[first contract]
,[previous contract]
,[current contract]
))pvt

4

copy the below code and try it should work

select * from (
select
person_id
,case
when rn = 1 then 'first contract'
when rn = 2 then 'previous contract'
when rn = 3 then 'current contract'
end as contract_type
,amount
from (
select
ROW_NUMBER() over (partition by person_id order by contract_id) rn,
*
from (
select
s.person_id,
s.contract_id,
c.amount
from db.contract c
JOIN db.subscription s ON ((c.person_id = s.person_id))
group by s.person_id, s.contract_id, c.amount
)main
)Fox
)ELma
pivot (sum(amount) for contract_type in (
[first contract]
,[previous contract]
,[current contract]
))pvt;

1 Like
5 
SELECT c.person_id, 
    c.contract_id AS current_contract, c.amount AS current_amount,
    s.first_contract_id, s.first_contract_amount,
    s.previous_contract_id, s.previous_contract_amount
FROM (
    select *, ROW_NUMBER() OVER(PARTITION BY person_id ORDER BY contract_id DESC) AS row_num
    from db.contract c2
) AS c
CROSS APPLY (
    SELECT 
        MAX(CASE WHEN c3.contract_id = s3.first_contract_id THEN s3.first_contract_id END) AS first_contract_id,
        MAX(CASE WHEN c3.contract_id = s3.first_contract_id THEN c3.amount END) AS first_contract_amount,
        MAX(CASE WHEN c3.contract_id = s3.previous_contract_id THEN s3.previous_contract_id END) AS previous_contract_id,
        MAX(CASE WHEN c3.contract_id = s3.previous_contract_id THEN c3.amount END) AS previous_contract_amount
    FROM db.subscription s3
    INNER JOIN db.contract c3 ON c3.contract_id IN (s3.first_contract_id, s3.previous_contract_id)
    WHERE s3.person_id = c.person_id
) AS s
WHERE c.row_num = 1
1 Like