CODEWITHSUNDEEP
sqlsql-server
Hidalgo
Hidalgo

Reputation: 941

SQL update with hard-coded column names

I need help creating a SQL update for the following example (with sample data):

Table 1: DETAILTRAN Structure: VENDOR CHAR(10), EMPLOYEE CHAR(10), WEEK INT

VENDOR   EMPLOYEE      WEEK
VEN01       EMP01         1
VEN01       EMP01         1
VEN02       EMP03         1
VEN03       EMP02         1
VEN01       EMP01         2
VEN01       EMP01         2
VEN01       EMP03         2
VEN03       EMP02         2
VEN02       EMP01         3
VEN02       EMP01         3
VEN02       EMP03         3
VEN03       EMP03         3

Table 2: SUMMARTRAN (that needs to be updated)

Structure: WEEK01 INT, WEEK02 INT, WEEK03 INT, VENDOR CHAR(10), EMPLOYEE CHAR(10) The results of SQL Update this table (SUMMARTRAN) should look like this:

WEEK01     WEEK02    WEEK03    VENDOR   EMPLOYEE
2          3         0         VEN01
1          0         3         VEN02
1          1         1         VEN03
2          2         2                  EMP01
1          1         0                  EMP02
1          1         2                  EMP03

Upvotes: 0

Views: 103

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You can approach this using grouping sets and conditional aggregation:

select vendor, employee,
       sum(case when week = 1 then 1 else 0 end) as week01,
       sum(case when week = 2 then 1 else 0 end) as week02,
       sum(case when week = 3 then 1 else 0 end) as week03
from DETAILTRAN
group by grouping sets ((vendor), (employee));

You can incorporate this into an insert, statement for summartran:

insert into summertran(vendor, employee, week01, week02, week03)
    select vendor, employee,
           sum(case when week = 1 then 1 else 0 end) as week01,
           sum(case when week = 2 then 1 else 0 end) as week02,
           sum(case when week = 3 then 1 else 0 end) as week03
    from DETAILTRAN
    group by grouping sets ((vendor), (employee));

Upvotes: 2

Pரதீப்
Pரதீப்

Reputation: 93694

Try this

with VENDOR as
(
select isnull(count(case when WEEK = 1 then WEEK end),0) WEEK01,
       isnull(count(case when WEEK = 2 then WEEK end),0) WEEK02 ,  
       isnull(count(case when WEEK = 3 then WEEK end),0) WEEK03 ,
VENDOR
from DETAILTRAN 
group by VENDOR
),EMPLOYEE as
(
select isnull(count(case when WEEK = 1 then WEEK end),0) WEEK01,
       isnull(count(case when WEEK = 2 then WEEK end),0) WEEK02 ,  
       isnull(count(case when WEEK = 3 then WEEK end),0) WEEK03 ,
EMPLOYEE
from DETAILTRAN 
group by EMPLOYEE
)
select WEEK01, WEEK02, WEEK03, VENDOR, EMPLOYEE = '' from VENDOR 
union all
select WEEK01, WEEK02, WEEK03, VENDOR='', EMPLOYEE from EMPLOYEE

To update the result to SUMMARTRAN table use this

with VENDOR as
(
select isnull(count(case when WEEK = 1 then WEEK end),0) WEEK01,
       isnull(count(case when WEEK = 2 then WEEK end),0) WEEK02 ,  
       isnull(count(case when WEEK = 3 then WEEK end),0) WEEK03 ,
VENDOR
from DETAILTRAN 
group by VENDOR
),EMPLOYEE as
(
select isnull(count(case when WEEK = 1 then WEEK end),0) WEEK01,
       isnull(count(case when WEEK = 2 then WEEK end),0) WEEK02 ,  
       isnull(count(case when WEEK = 3 then WEEK end),0) WEEK03 ,
EMPLOYEE
from DETAILTRAN 
group by EMPLOYEE
)
insert into  SUMMARTRAN (WEEK01, WEEK02, WEEK03, VENDOR, EMPLOYEE)
select WEEK01, WEEK02, WEEK03, VENDOR, EMPLOYEE = '' from VENDOR 
union all
select WEEK01, WEEK02, WEEK03, VENDOR='', EMPLOYEE from EMPLOYEE

Upvotes: 1

Related Questions