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

sql-return.PNG
aj85Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ryan ChongBusiness Systems Analyst , ex-Senior Application EngineerCommented:
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.
1
PortletPaulEE Topic AdvisorCommented:
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?
1
Mousa BadamchizadehDB-ADMINCommented:
Add
Where loc='NY'
If other values dont need.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

PortletPaulEE Topic AdvisorCommented:
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
0
Mark WillsTopic AdvisorCommented:
Hmmm... I dont think UNION is the right choice in these circumstances.


Judging by what you are exposing in the UNION, we get most of the *real* data  LOC, DATE, [DESC] for a primary location which also stores ITEM1. That is from a primary table PRODUCT with ID as a primary key.

There is a secondary table PRODUCTS2 also with a primary key of ID used for additional locations LA and SA storing values for ITEM2 and ITEMS3 respectively.  It could have location in there as well, but have to make some assumptions and the important one is ID joins Primary Location PRODUCT with Secondary Locations PRODUCTS2.

Now the unknown is the ITEM columns... whether or not there are three differently named ITEM columns, or, the locations really determine the column names....

Would help to see the DDL (Data Definition Language = table structure) of PRODUCTS and PRODUCTS2 with sample data to make sure of data relationships....

Assuming that scenario makes sense (because they all have different column names) then I think you just need a straight GROUP BY query.

SELECT a.ID, a.LOC, a.DATE, a.[DESC] as Description, sum(a.Item1) as Available, sum(b.Item2) as [LA Available], sum(b.Items3) as [SA Available] 
FROM Products a
INNER JOIN Products2 b on a.ID=b.ID
GROUP BY a.ID, a.LOC, a.DATE, a.[DESC]
ORDER BY a.ID;

Open in new window


 And by way of testing
select 12345 ID , 'NY' LOC, '1 sep 18' DATE , 'Sony' [DESC] , 34 [Item1] into #Products
	
select 12345 ID ,  73 [Item2], 12 as [Items3] into #Products2


SELECT a.ID, a.LOC, a.DATE, a.[DESC] as Description, sum(a.Item1) as Available, sum(b.Item2) as [LA Available], sum(b.Items3) as [SA Available] 
FROM #Products a
INNER JOIN #Products2 b on a.ID=b.ID
GROUP BY a.ID, a.LOC, a.DATE, a.[DESC]
ORDER BY a.ID;

Open in new window

giving your desired result of
ID          LOC  DATE     Description Available   LA Available SA Available
----------- ---- -------- ----------- ----------- ------------ ------------
12345       NY   1 sep 18 Sony        34          73           12

(1 row affected)

Open in new window

Now, it doesnt fully explain your results, because grouping by a.LOC generates the different values NY, LA, SA so there is a bit more to unravel in terms of the relationship between the two tables and how you were able to get a value for ITEM2 against  LA  when the LOC for ITEM2 was cast as NULL.

So maybe a bit more work, or, conditional aggregates (rather than group by a.loc) e.g.  
SUM(CASE when a.LOC = 'NY' then a.ITEM else 0 end) as [Available],
SUM(CASE when a.LOC = 'LA' then b.ITEM2 else 0 end) as [LA Available],
SUM(CASE when a.LOC = 'SA' then b.ITEMS3 else 0 end) as [SA Available]
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
G Trurab KhanSnr. Development ManagerCommented:
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;
0
aj85Author 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
0
aj85Author Commented:
Worked extremely well, exactly as indicated.
0
Mark WillsTopic AdvisorCommented:
Great to hear... And very happy to have been able to help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.