We help IT Professionals succeed at work.

SQL UNION

84 Views
Last Modified: 2018-09-20
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

sql-return.PNG
Comment
Watch Question

CERTIFIED EXPERT

Commented:
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.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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?

Commented:
Add
Where loc='NY'
If other values dont need.
PortletPaulEE Topic Advisor
CERTIFIED EXPERT
Most Valuable Expert 2014
Awarded 2013

Commented:
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
Topic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION
G Trurab KhanSnr. Development Manager
CERTIFIED EXPERT

Commented:
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;

Author

Commented:
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

Author

Commented:
Worked extremely well, exactly as indicated.
Mark WillsTopic Advisor, Page Editor
CERTIFIED EXPERT
Distinguished Expert 2018

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions