sql - How to JOIN a table with itself and display it as a single row -
there table named product_price:
create table [test].[product_price] ( [price_id] [bigint] not null, [product_id] [bigint] not null, [price_date] [date] not null, [is_sale_price] [bit] not null, [unit_price] [decimal](18, 2) not null ) it has following records:
price_id product_id price_date is_sale_price unit_price -------- ---------- ---------- ------------- ---------- 1 15 2015-05-12 false 0,05 2 15 2015-05-12 true 0,04 3 25 2015-05-12 false 1,45 4 35 2015-05-12 true 2,65 edit: there can 2 prices - purchase price , sale price. there can't 3 or more rows same product_id , price_date.
i want write select statement results in following:
price_id product_id price_date is_sale_price unit_price price_id_2 is_sale_price_2 unit_price_2 -------- ---------- ---------- ------------- ---------- ---------- --------------- ------------ 1 15 2015-05-12 false 0,05 2 true 0,04 3 25 2015-05-12 false 1,45 null null null 4 35 2015-05-12 true 2,65 null null null i tried full outer join results in 4 rows rather 3 correct not looking for:
select pr1.*, pr2.price_id price_id_2, pr2.is_sale_price is_sale_price_2, pr2.unit_price unit_price_2 product_price pr1 full outer join product_price pr2 on pr1.product_id = pr2.product_id , pr1.price_date = pr2.price_date , pr1.price_id <> pr2.price_id , pr1.is_sale_price <> pr2.is_sale_price pr1.price_date = '20150512' order pr1.price_id result of above query:
| price_id | product_id | price_date | is_sale_price | unit_price | price_id_2 | is_sale_price_2 | unit_price_2 | |----------|------------|------------|---------------|------------|------------|-----------------|--------------| | 1 | 15 | 2015-05-12 | false | 0.05 | 2 | true | 0.04 | | 2 | 15 | 2015-05-12 | true | 0.04 | 1 | false | 0.05 | | 3 | 25 | 2015-05-12 | false | 1.45 | (null) | (null) | (null) | | 4 | 35 | 2015-05-12 | true | 2.65 | (null) | (null) | (null) | basically want join table , remove duplicates.
note: price_id identity field (primary key). natural key product_id, price_date pair. want row each unique product_id , price_date.
if you're sure there maximum of 2 rows each product_id - price_date combination, can use conditional aggregation instead of join:
select price_id = max(case when rn = 1 price_id end), product_id, price_date, is_sale_price = max(case when rn = 1 cast(is_sale_price int) end), unit_price = max(case when rn = 1 unit_price end), price_id2 = max(case when rn = 2 price_id end), is_sale_price2 = max(case when rn = 2 cast(is_sale_price int) end), unit_price2 = max(case when rn = 2 unit_price end) ( select *, rn = row_number() over(partition product_id, price_date order is_sale_price) product_price )t group product_id, price_date order product_id, price_date result
| price_id | product_id | price_date | is_sale_price | unit_price | price_id2 | is_sale_price2 | unit_price2 | |----------|------------|------------|---------------|------------|-----------|----------------|-------------| | 1 | 15 | 2015-05-12 | 0 | 0.05 | 2 | 1 | 0.04 | | 3 | 25 | 2015-05-12 | 0 | 1.45 | (null) | (null) | (null) | | 4 | 35 | 2015-05-12 | 1 | 2.65 | (null) | (null) | (null) | if insist on using join, can use full join:
select price_id = case when pp.price_id not null pp.price_id else sp.price_id end, product_id = case when pp.price_id not null pp.product_id else sp.product_id end, price_date = case when pp.price_id not null pp.price_date else sp.price_date end, is_sale_price = case when pp.price_id not null pp.is_sale_price else sp.is_sale_price end, unit_price = case when pp.price_id not null pp.unit_price else sp.unit_price end, price_id2 = case when pp.price_id not null sp.price_id end, is_sale_price2 = case when pp.price_id not null sp.is_sale_price end, unit_price2 = case when pp.price_id not null sp.unit_price end ( select * product_price is_sale_price = 0 )as pp full join( select * product_price is_sale_price = 1 )as sp on pp.product_id = sp.product_id , pp.price_date = sp.price_date order product_id, price_date
Comments
Post a Comment