SQL UNION

try customize something like this:

;with cte as
(
	select 12345 ID , 'NY' LOC, '1 sep 18' DATE , 'Sony' [DESC] , 34 [Item 1], NULL [Item 2], NULL [Item 3]
	union
	select 12345 ID , 'LA' LOC, '1 sep 18' DATE , 'Sony' [DESC] , NULL [Item 1], 73 [Item 2], NULL [Item 3]
	union
	select 12345 ID , 'SA' LOC, '1 sep 18' DATE , 'Sony' [DESC] , NULL [Item 1], NULL [Item 2], 12 [Item 3]
), cte2 as
(
	Select *, ROW_NUMBER() over (order by (Select 1)) idx from cte
)
select
a.ID, a.LOC, a.DATE, a.[DESC],
max(b.[Item 1]) [Item 1], max(b.[Item 2]) [Item 2], max(b.[Item 3]) [Item 3] 
from cte2 a left join cte b on a.ID = b.ID
Where a.idx = 1
Group By a.ID, a.LOC, a.DATE, a.[DESC]

Select allOpen in new window

put your original scripts into "with cte as" and amend the field names accordingly.

Please note that the core of what you need to solve the Item1,Item2 and Item3 columns is  to use aggregate functions such as MAX() for the Item1,Item2 and Item3 columns and a GROUP BY clause.

BUT

Your image reveals that you would NOT get to a single row. The second column "loc" has 3 different values, and that will lead to 3 rows.

How do you propose to deal with that second column?
e.g. just ignore it? Take the maximum? take the minimum?  concatenate into a comma separated string?

Add
Where loc='NY'
If other values dont need.

BUT: if you filter out the non-matching data then you won't get anything listed in Item2 or Item3 of that image.

12345 NY 09/01/18 Sony 34 NULL NULL

Try this code

SELECT a.ID, a.LOC, a.DATE, a.DESC as Description, sum(a.Item1) as Available, sum(b.Item2) as LAAvailable, sum(b.Items3) as SAAvailable FROM Products a, Products2 b
WHERE a.ID=b.ID
GROUP BY a.ID, a.LOC, a.DATE, a.DESC
UNION
SELECT NULL, NULL, NULL, NULL, NULL, sum(b.Item2) as LAAvailable, sum(b.Items3)
 as SAAvailable FROM Products a, Products2 b
WHERE a.ID=b.ID
ORDER BY ID;

Hello Mark,

Your advice solved my problem! :)  Thank you very much, and also thank you to everyone else that offered their advice, as well.  I simply could not get this to work as required. Again, thanks everyone.

aj85

Worked extremely well, exactly as indicated.

Great to hear... And very happy to have been able to help.