combine two table in sql server

Question

i have m_peserta table.

no_test | name
-----------------------
11      | Mr. X 
21      | Mr. Y 
31      | Mr. Z 
41      | Mr. P

I have result table

no_test     | quest_cat | answer    
-------------------------------------------
11          | Type I-2  | 1 
11          | Type I-5  | 1 
11          | Type I-6  | 1 
11          | Type II-2 | My Answer is bla
11          | Type II-4 | My Answer is bla 
21          | Type I-5  | 1 
21          | Type I-6  | 1 
21          | Type II-3 | My Answer is bla
21          | Type II-1 | *note : this is a Null Answer 
21          | Type III-1| *note : this is a Null Answer 
21          | Type III-3| My Answer is bla

and i have a empty table named combine_table with the column is no_test, name, count_quest_I, count_quest_II, count_quest_III.

And i need query "INSERT" from combine table m_peserta and result, so i have a combine_table like this

no_test | name  | count_quest_I | count_quest_II | count_quest_III 
-----------------------------------------------------
11      | Mr. X | 3             | 2              | 0        
21      | Mr. Y | 2             | 1              | 1

please help, thank you

1. what database are you using (mysql or sql-server) they have two different dialects. 2. why do you need to insert into a new table the join? can't you just do it on select? the way you are doing it will be a nightmare on update — Lelio Faieta, Feb 11 '19 at 09:59
What have you tried so far? Please do share your attempt(s). — Thom A, Feb 11 '19 at 10:00
Why aren't you just creating a View instead of keeping a calculated table with duplicities in your database? — Angel M., Feb 11 '19 at 10:25
You should use PIVOT with your query. https://stackoverflow.com/questions/15931607/convert-rows-to-columns-using-pivot-in-sql-server — JERRY, Feb 11 '19 at 10:25

score 0 · Answer 1 · answered Feb 11 '19 at 10:00

A join with group by will help.

see query below

insert into combine_table 
   (a.no_test,
    a.name,
    count_quest_I,
    count_quest_II,
    count_quest_III)
select 
    a.no_test,
    a.name,
    count_quest_I = sum(case when quest_cat like 'Type I-%' then 1 else 0 end),
    count_quest_II = sum(case when quest_cat like 'Type II-%' then 1 else 0 end),
    count_quest_III = sum(case when quest_cat like 'Type III-%' then 1 else 0 end)
from
    m_peserta  a join result r
        on a.no_test=r.no_test
group by 
    a.no_test,a.name

score 0 · Accepted Answer · answered Feb 11 '19 at 10:03

You can try like following.

;WITH cte 
     AS (SELECT no_test, 
                Sum(CASE 
                      WHEN quest_cat LIKE 'Type I-%' THEN 1 
                      ELSE 0 
                    END) AS count_quest_I, 
                Sum(CASE 
                      WHEN quest_cat LIKE 'Type II-%' THEN 1 
                      ELSE 0 
                    END) AS count_quest_II, 
                Sum(CASE 
                      WHEN quest_cat LIKE 'Type III-%' THEN 1 
                      ELSE 0 
                    END) AS count_quest_III 
         FROM   result 
         GROUP  BY no_test) 
INSERT INTO combine_table 
SELECT t1.no_test, 
       t1.NAME, 
       count_quest_i, 
       count_quest_ii, 
       count_quest_iii 
FROM   m_peserta t1 
       INNER JOIN cte r 
               ON r.no_test = t1.no_test

this is almost perfect, but I need if there is an empty answer then it doesn't count — Lukman Azhari, Feb 11 '19 at 10:20

combine two table in sql server

2 Answers2