0

I have multiple tables (12 for each month) that have 3 columns each ("code", "test_name", "value")

Example:


January
code | test_name | value
0001   name1       17
0002   name2       45
0005   name5       12

February
code | test_name | value
0001   name1       3
0002   name2       7
0004   name4       13

March
code | test_name | value
0001   name1       6
0006   name6       32
0007   name7       41

How do I merge or insert or combine it in sql into one table that would look like this:

Year
code | test_name | January | February | March | ...
0001   name1       17        3          6
0002   name2       45        7          0
0003   name3       0         0          0
0004   name4       0         13         0
0005   name5       12        0          0
0006   name6       0         0          32
0007   name7       0         0          41
1
  • @a_horse_with_no_name Microsoft SQLServer Management Studio 2008R2
    – Mladen G
    Mar 15 '16 at 19:45
0

I wrote this for SQL 2012, but it should work in 2008R2 and up.

DECLARE @January AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @January
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 17 )
    ,   ( '0002', 'name2', 45 )
    ,   ( '0005', 'name5', 12 );

DECLARE @February AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @February
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 3 )
    ,   ( '0002', 'name2', 7 )
    ,   ( '0004', 'name4', 13 );

DECLARE @march AS TABLE
    (
      Code CHAR(4) NOT NULL
    , test_name VARCHAR(100) NOT NULL
    , value INT NOT NULL
    );

INSERT  INTO @march
        ( Code, test_name, value )
VALUES  ( '0001', 'name1', 6 )
    ,   ( '0006', 'name6', 32 )
    ,   ( '0007', 'name7', 41 );


WITH CTE_AllMonths AS
    (
    SELECT Code, test_name, value, 'January' AS SourceMonth
    FROM @January
    UNION ALL
    SELECT Code, test_name, value, 'February' AS SourceMonth
    FROM @February 
    UNION ALL
    SELECT Code, test_name, value, 'March' AS SourceMonth
    FROM @March  
    )
    SELECT Code, test_name, January, February, March
    FROM CTE_AllMonths 
        PIVOT (SUM(value) FOR SourceMonth IN ([January], [February], [March])) AS PT
0

The CTE is more elegant, but you could also use CASE

CREATE TABLE #January (code varchar(4), test_name varchar(5), value INT)
CREATE TABLE #February (code varchar(4), test_name varchar(5), value INT)
CREATE TABLE #March (code varchar(4), test_name varchar(5), value INT)

INSERT INTO #January VALUES ('0001','name1',17)
INSERT INTO #January VALUES ('0002','name2',45)
INSERT INTO #January VALUES ('0005','name5',12)

INSERT INTO #February VALUES ('0001','name1',3)
INSERT INTO #February VALUES ('0002','name2',7)
INSERT INTO #February VALUES ('0004','name4',13)

INSERT INTO #March VALUES ('0001','name1',6)
INSERT INTO #March VALUES ('0006','name6',32)
INSERT INTO #March VALUES ('0007','name7',41)

SELECT 
code, test_name
, SUM(ISNULL(CASE WHEN [Month] = 'January' THEN VALUE END,0)) AS 'January'
, SUM(ISNULL(CASE WHEN [Month] = 'February' THEN VALUE END,0)) AS 'February'
, SUM(ISNULL(CASE WHEN [Month] = 'March' THEN VALUE END,0)) AS 'March'
FROM (
SELECT 'January' AS 'Month', * FROM #January
UNION ALL
SELECT 'February' AS 'Month', * FROM #February
UNION ALL
SELECT 'March' AS 'Month', * FROM #March
) a
GROUP BY a.code, a.test_name
ORDER BY a.code, a.test_name


DROP TABLE #January
DROP TABLE #February
DROP TABLE #March
0

In the end I went with this solution and it's just what I was looking for(solved the problem):

select Sifra, Naziv_pretrage,
       max(jan) as jan, max(feb) as feb, max(mar) as mar, max(apr) as apr, max(maj) as maj, max(jun) as jun, max(jul) as jul, max(avg) as avg, max(sep) as sep, max(okt) as okt, max(nov) as nov, max(dec) as dec, max(jan16) as jan16
from (
            (select Sifra, Naziv_pretrage, Ukupno as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Januar
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, Ukupno as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Februar
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, Ukupno as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Mart
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, Ukupno as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from April
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, Ukupno as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Maj
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, Ukupno as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Jun
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, Ukupno as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Jul
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, Ukupno as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Avgust
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, Ukupno as Sep, NULL as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Septembar
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, Ukupno as Okt, NULL as Nov, NULL as Dec, NULL as Jan16
            from Oktobar
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, Ukupno as Nov, NULL as Dec, NULL as Jan16
            from Novembar
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, Ukupno as Dec, NULL as Jan16
            from Decembar
            ) union all
            (select Sifra, Naziv_pretrage, NULL as Jan, NULL as Feb, NULL as Mar, NULL as Apr, NULL as Maj, NULL as Jun, NULL as Jul, NULL as Avg, NULL as Sep, NULL as Okt, NULL as Nov, NULL as Dec, Ukupno as Jan16
            from Januar16
            )   
        ) ct
group by Sifra, Naziv_pretrage
order by Sifra asc;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.