Search code examples
sqlsql-servermergeinner-join

Merging rows to one row with inner join in SQL Server

I have a products table in SQL Server like this:

ID      ItemCode        ProductName
------  ------------    -----------------
1       8680972060039   test product
2       123457          coffee
3       123458          tea

and I have a price table for these products like this:

PriceListLineID                         ItemCode        Price       PriceGroupCode  LastUpdatedDate
------------------------------------    --------------  ---------   --------------  ------------------------
D7B4F7A5-3CEF-4FFB-B90A-A81700D6CCE8    8680972060039   79,00       B2C             2017-10-25 15:44:30.640
3456748E-FA54-4683-83E7-A81700D6CC40    8680972060039   75,00       B2B             2017-10-25 15:44:30.117
CF03A676-4276-432B-BA7B-A76A0128D9B6    8680972060039   124,00      B2C             2017-05-05 20:47:05.197
5257C168-365A-475E-B2FD-A76A0128D8EF    8680972060039   118,00      B2B             2017-05-05 20:47:04.630

I want to show last updated both b2c and b2b prices near with product details like

ItemCode    ProductName         B2BPrice    B2CPrice
------------    -------------   ---------   -----------
8680972060039   test product    79,00       75,00

I use one inner join to get prices but i can not get second price with it how can i do it ?

This is the SQL code I use:

select 
    Products.ItemCode, Products.ProductName, Prices.Price 
from 
    Products WITH(NOLOCK)
inner join
    Prices with(nolock) on Prices.ItemCode = Products.ItemCode 
                        and Prices.PriceGroupCode = 'B2B'  
                        and Prices.Price is not null
order by
    Prices.LastUpdated desc
Solution

  • Can you please try the query below, I think top 1 trick will help:

    select Products.ItemCode, Products.ProductName,
       B2BPrice = (Select top 1 p.Price from Prices p where p.ItemCode = Products.ItemCode and p.PriceGroupCode='B2B' order by LastUpdatedDate desc),
       B2CPrice = (Select top 1 p.Price from Prices p where p.ItemCode = Products.ItemCode and p.PriceGroupCode='B2C' order by LastUpdatedDate desc)
from Products WITH(NOLOCK)
INNER JOIN Prices with(nolock) on Prices.ItemCode = Products.ItemCode