CODEWITHSUNDEEP
sql-server
Dave
Dave

Reputation: 1852

How merge two rows into one and allow for nulls

I've seen similar examples and solutions here which let me get this far (thanks) but can't figure out the last step. Ideally the solution is efficient for combining several hundred thousand rows.

Basically I have a table of price data with 1 or 2 rows for each item with different types of price data - price type 1 and price type 2. I would like to end up with a table/view that combine the 2 prices onto one row but kept the relative position of each and allowed for nulls on the price 2 data.

So if I have this data:

CREATE TABLE Table1 ([Name] varchar(2), [type] char(1), [price] decimal(10,2));

INSERT INTO Table1 ([Name], [type], [price])
VALUES
    ('a', '1', '1.20'),
    ('a', '2', '1.25'),
    ('a1','1', '2.99'),
    ('b', '1', '2.20'),
    ('b', '2', '2.25'),
    ('b1','2', '3.99'),
    ('c', '1', '3.20'),
    ('c', '2', '3.25'),
    ('d', '1', '4.20'),
    ('d', '2', '4.25');

I can run this SQL:

select name, [1] price_1, [2] price_2
from
(
  select name,
         price,
         row_number() over (partition by name
                            order by type asc) rn
    from table1
) o
pivot (min(price) for rn in ([1], [2])) p

And I get the following output which is not quite right for what I want.

NAME PRICE_1  PRICE_2
a    1.2      1.25
a1   2.99     (null)
b    2.2      2.25
b1   3.99     (null)
c    3.2      3.25
d    4.2      4.25

What I need is for the a1 and b1 rows to have the null in the price_1 column and the price in the price_2 column.

Upvotes: 3

Views: 12516

Answers (3)

Andriy M
Andriy M

Reputation: 77737

I don't really understand why you are using ROW_NUMBER. You could just use the type column instead:

select name, [1] price_1, [2] price_2
from
(
  select name,
         price,
         row_number() over (partition by name
                            order by type asc) type rn
    from table1
) o
pivot (min(price) for rn in ([1], [2])) p

SQL Fiddle demo

Upvotes: 0

Charles Bretana
Charles Bretana

Reputation: 146603

Full join does this trick...

Insert newTable(name, PriceA, PriceB)
Select coalesce(a.Name, b.Name) Name,
    a.Price, b.price
From oldTable a Full Join oldTable b
   On b.name = a.Name
       and a.Type = [PricetTypeA]
       and b.Type = [PricetTypeB]

or, if pricetype is a string = 'A' or 'B',

Insert newTable(name, PriceA, PriceB)
Select coalesce(a.Name, b.Name) Name,
    a.Price, b.price
From oldTable a Full Join oldTable b
   On b.name = a.Name
       and a.Type = 'A'
       and b.Type = 'B'

Upvotes: 3

Dave
Dave

Reputation: 1852

Charles gave me the idea of using a full join and after playing around I got this to work.

Select coalesce(a.Name, b.name) as name, a.Price as price1, b.price as price2
From      (select name, price from table1 where type='1') a
full Join (select name, price from table1 where type='2') b
On b.name = a.Name

and this works fine using as a view.

Glad to have any other suggestions and comments.

Upvotes: 2

Related Questions