SQL UNION

aj85
aj85 used Ask the Experts™
on
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

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

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
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.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

PortletPaulEE Topic Advisor
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
Distinguished Expert 2018
Commented:
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]
G Trurab KhanSnr. Development Manager

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
Distinguished Expert 2018

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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial