asked on

SQL UNION

I have several tables that I need to gather certain information from and roll the data into a single row. I am currently trying to accomplish this with a Union, but for some reason, the data is returning in several rows, and not the roll-up I am wanting, so that all of the information is on a single row, with the column data. Here is my current script, and how the data is showing:

SELECT a.ID, a.LOC, a.DATE, a.DESC as Description, a.Item1 as Available, b.Item2 as LA Available, b.Items3 as SA Available FROM Products a, Products2 b
WHERE a.ID=b.ID
UNION
SELECT NULL, NULL, NULL, NULL, NULL, b.Item2 as LA Available, b.Items3 as SA Available FROM Products a, Products2 b
WHERE a.ID=b.ID
GROUP BY a.ID, a.LOC, a.DATE, a.DESC, a.Item1, b.Item2, b.Items3
ORDER BY ID;

Open in new window

Ryan Chong

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]

Open in new window

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

PortletPaul

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?

Mousa Badamchizadeh

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

PortletPaul

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

ASKER CERTIFIED SOLUTION

Mark Wills

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

G Trurab Khan

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;

aj85

ASKER

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

aj85

ASKER

Worked extremely well, exactly as indicated.

Mark Wills

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