How to insert data from 4 queries into one table when the 4 queries have data that are not unique?

Question

to be more clear I have these kind of data.

Query 1) Data from 2016

Item       Price        Quantity

Shoe        20             10
Shoe        30             15
Cups        10             30
Towels      30             20
Towels      25             20
Towels      20             20

Query 2) Data from 2017

Item       Price        Quantity

Shoe        40             30
Shoe        50             20
Towels      30             30
Towels      20             30

Query 3) Data from 2018

Item       Price        Quantity

Shoe        20             10
Cups        10             30
Towels      30             20
Towels      25             20
Towels      20             20

Query 1) Data from 2019

Item       Price        Quantity

Shoe        20             10
Shoe        30             15
Cups        10             30
Towels      30             20
Towels      25             20
Towels      20             20

I would like to have a result like this:

Item   Price2016   Quantity2016   Price2017   Quantity2017  Price2018   Quantity2018   Price2019   Quantity2019

Shoe      20           10            40            30          20          10             20            10
Shoe      30           15            50            20                                     30            15

I tried using Joins, Unions, even creating Temp Tables or a cursor

for example, inner Join produce this result:

Item       Price2016        Quantity 2016       Price2017        Quantity 2017 ...

Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10
Shoe          20                 10                20               10

Please do take note that the data in this example is innacurate but the result is similar to this.

Any Idea how can I obtain my preferred result using SQL

EDIT: The query that I get the data from is this

select Item, Price, sum(quantity) as quantity from Sales where year(itemsold) = 2016 group by Item, price

I just change the year to get the other data.

Please include your tables and definitions. Note: You just need a pivot query, and this question is a duplicate or so many others. — Tim Biegeleisen, May 14 '19 at 05:23
@TimBiegeleisen arent my example enough for the question? I just need to get an idea, and now I'm checking on pivot query... will try that now, thank you for the idea! — Mr.J, May 14 '19 at 05:52
@utor its just the summation of the total sales of the item for the specific year. — Mr.J, May 14 '19 at 05:54
@TimBiegeleisen Pivot gets the sum of the item sold for the year, I can't seem to see how I could try and get the data that I would want to... — Mr.J, May 14 '19 at 05:56
you can consider using [dynamic pivoting](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) — Barbaros Özhan, May 14 '19 at 06:55

score 1 · Answer 1 · answered May 14 '19 at 06:55

1

Try use it, It includes GROUP BY the items and the prices.

SELECT Item,
Price,
SUM(Quantity2016),
SUM(Quantity2017),
...
FROM
(
    --query 1:

    SELECT Item, 
    Price,
    Quantity AS Quantity2016,
    NULL AS Quantity2017  
    NULL AS Quantity2018   
    NULL AS Quantity2019
    FROM 2016

    UNION ALL

    --query 2:

    SELECT Item, 
    Price,
    NULL AS Quantity2016,
    Quantity AS Quantity2017  
    NULL AS Quantity2018   
    NULL AS Quantity2019
    FROM 2017

    UNION ALL

    --query 3:

    SELECT Item, 
    Price,
    NULL AS Quantity2016,
    NULL AS Quantity2017  
    Quantity AS Quantity2018   
    NULL AS Quantity2019
    FROM 2018

    UNION ALL
    ...

)A
GROUP BY Item, Price

answered May 14 '19 at 06:55

1

Sorry for my last comment, do you have any idea how to order them by year? The result I am getting is controlled by the group by clause so, the quantity for 2016 is at the last row of the column of 2016, while the quantity of 2019 is at the first row at the column of 2019, is it possible to make something like, the column of 2016 will have its data on the first row since its the first column? – Mr.J May 14 '19 at 07:38
@Mr.J. First you can add `ORDER BY Quantity2016, Quantity2017 ...` – May 14 '19 at 07:42
Any other Ideas? I tried your comment but the result is the same. – Mr.J May 14 '19 at 07:49
@Mr.J. Isn't it because the compiler orders it automatically by the lower price? – May 14 '19 at 07:57
It does seems like it... but the problem is, sometimes the lower prices are in between, let say 2018 has the lower price of some items. – Mr.J May 14 '19 at 08:00

Nikunj Satasiya · Accepted Answer · 2019-05-18T09:59:20.500

J,

I have seen the above solution and it is also valid but you also can try using PIVOT. I have created a demo for you, please check this solution also that might helps you.

DEMO

DECLARE TABLES & INSERT RECORDS

DECLARE @Table2016 AS TABLE ( Item VARCHAR(50), Price FLOAT, Quantity INT ); 
DECLARE @Table2017 AS TABLE ( Item VARCHAR(50), Price FLOAT, Quantity INT ); 
DECLARE @Table2018 AS TABLE ( Item VARCHAR(50), Price FLOAT, Quantity INT );
DECLARE @Table2019 AS TABLE ( Item VARCHAR(50), Price FLOAT, Quantity INT );

INSERT INTO @Table2016 (Item,Price,Quantity) VALUES
('Shoe'  ,20,10),
('Shoe'  ,30,15),
('Cups' ,10,30),
('Towels',30,20),
('Towels',25,20),
('Towels',20,20)

INSERT INTO @Table2017 (Item,Price,Quantity) VALUES
('Shoe'  ,40,30),
('Shoe'  ,50,20),
('Towels',30,30),
('Towels',20,30)

INSERT INTO @Table2018 (Item,Price,Quantity) VALUES
('Shoe'  ,20,10),
('Cups'  ,10,30),
('Towels',30,20),
('Towels',25,20),
('Towels',20,20)

INSERT INTO @Table2019 (Item,Price,Quantity) VALUES
('Shoe'  ,20,10),
('Shoe'  ,30,15),
('Cups'  ,10,30),
('Towels',30,20),
('Towels',25,20),
('Towels',20,20)

MARGE ALL TABLES AND INSERT INTO TEMP TABLE

    SELECT Item,Price,Quantity,PriceYear,QuantityYear INTO TempFinal
    FROM (
    SELECT Item,Price,Quantity, 'Price2016' as PriceYear,'Quantity2016' as QuantityYear FROM @Table2016
    UNION ALL                                      
    SELECT Item,Price,Quantity, 'Price2017' as PriceYear,'Quantity2017' as QuantityYear FROM @Table2017
    UNION ALL                                      
    SELECT Item,Price,Quantity, 'Price2018' as PriceYear,'Quantity2018' as QuantityYear FROM @Table2018
    UNION ALL                                      
    SELECT Item,Price,Quantity, 'Price2019' as PriceYear,'Quantity2019' as QuantityYear FROM @Table2019
    ) MyTables

QUERY WITHOUT GROUPBY

    SELECT item, [Price2016],[Quantity2016],[Price2017],[Quantity2017],[Price2018],[Quantity2018],[Price2019],[Quantity2019]
    FROM (
    SELECT item,Price,Quantity,PriceYear,QuantityYear
    FROM TempFinal) up
    PIVOT (SUM(Quantity) FOR QuantityYear IN ([Quantity2016],[Quantity2017],[Quantity2018],[Quantity2019])) AS pvt
    PIVOT (SUM(Price) FOR PriceYear IN ([Price2016],[Price2017],[Price2018],[Price2019])) AS pvt2
    ORDER BY item

QUERY WITH GROUPBY

     SELECT item, SUM([Price2016])[Price2016],SUM([Quantity2016])[Quantity2016],SUM([Price2017])[Price2017],SUM([Quantity2017])[Quantity2017],SUM([Price2018])[Price2018],SUM([Quantity2018])[Quantity2018],SUM([Price2019])[Price2019],SUM([Quantity2019])[Quantity2019]
    FROM (
    SELECT item,Price,Quantity,PriceYear,QuantityYear
    FROM TempFinal) up
    PIVOT (SUM(Quantity) FOR QuantityYear IN ([Quantity2016],[Quantity2017],[Quantity2018],[Quantity2019])) AS pvt
    PIVOT (SUM(Price) FOR PriceYear IN ([Price2016],[Price2017],[Price2018],[Price2019])) AS pvt2
    GROUP by item
    ORDER BY item

DROP TEMP TABLE

DROP TABLE TempFinal

score 0 · Answer 3 · answered May 14 '19 at 06:20

Try this as just an idea-

SELECT Item,
SUM(Price2016),SUM(Quantity2016),
SUM(Price2017),SUM(Quantity2017),
SUM(Price2018),SUM(Quantity2018),
SUM(Price2019),SUM(Quantity2019)
FROM
(
    SELECT Item, 
    Price Price2016, Quantity  Quantity2016,
    NULL Price2017,NULL   Quantity2017  
    NULL Price2018,NULL   Quantity2018   
    NULL Price2019, NULL  Quantity2019
    FROM 2016

    UNION ALL

    SELECT Item, 
    NULL Price2016, NULL Quantity2016,
    Price Price2017,Quantity Quantity2017  
    NULL Price2018,NULL   Quantity2018   
    NULL Price2019, NULL  Quantity2019
    FROM 2017

    UNION ALL

    SELECT Item, 
    NULL Price2016, NULL  Quantity2016,
    NULL Price2017,NULL   Quantity2017  
    Price Price2018,Quantity   Quantity2018   
    NULL Price2019, NULL  Quantity2019
    FROM 2018

    UNION ALL

    SELECT Item, 
    NULL Price2016, NULL Quantity2016,
    NULL Price2017,NULL   Quantity2017  
    NULL Price2018,NULL   Quantity2018   
    Price Price2019, Quantity Quantity2019
    FROM 2019
)A
GROUP BY Item

I added additional information, union all does not meet the requirement that I would like to have... — Mr.J, May 14 '19 at 06:36

score 0 · Answer 4 · answered May 14 '19 at 08:53

You can try following query, I have used T2016, T2017 etc as table names for year specific data.

select
T16.Item
,T16.Price [Price2016]
,T16.Quantity [Quantity2016]
,T17.Price [Price2017]
,T17.Quantity [Quantity2017]
,T18.Price [Price2018]
,T18.Quantity [Quantity2018]
,T19.Price [Price2019]
,T19.Quantity [Quantity2019]
from 
(select *, ROW_NUMBER() over(Partition by Item order by Price) [row] from T2016) T16
left join (select *, ROW_NUMBER() over(Partition by Item order by Price) [row] from     T2017) T17 on T16.Item=T17.Item and T16.row=T17.row
left join (select *, ROW_NUMBER() over(Partition by Item order by Price) [row] from T2018) T18 on T16.Item=T18.Item and T16.row=T18.row
left join (select *, ROW_NUMBER() over(Partition by Item order by Price) [row] from T2019) T19 on T16.Item=T19.Item and T16.row=T19.row

How to insert data from 4 queries into one table when the 4 queries have data that are not unique?

4 Answers4

Linked