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 bWHERE a.ID=b.IDUNIONSELECT NULL, NULL, NULL, NULL, NULL, b.Item2 as LA Available, b.Items3 as SA Available FROM Products a, Products2 bWHERE a.ID=b.IDGROUP BY a.ID, a.LOC, a.DATE, a.DESC, a.Item1, b.Item2, b.Items3ORDER BY ID;
Open in new window
put your original scripts into "with cte as" and amend the field names accordingly.