0

I have a table with the structure below :

Table(customer_Num  int , Channel_Id  Varchar(50) ,  Branch_Code  int)

and some example data for this table is :

                  Table
__________________________________________
customer_Num  |  Channel_Id  |  Branch_Code 
    1         |    'I'       |     1000
    1         |    'Y'       |     1001
    1         |    'L'       |     1002

and the final result I want in the output is :

                          Result_table
__________________________________________________________
customer_Num  | I_branch_Code |  Y_branch_Code  | L_Branch_Code
    1               1000             1001             1002

is there a better solution rather than using continuous Insert queries? Cause with this query I will have many nulls in my final table!And I also won't have the result in one record.

 insert into Result_table (customer_Num  ,I_branch_Code )
 select customer_Num   , Branch_Code 
 from Table
 where Channel_Id   = 'I';

 insert into Result_table (customer_Num  ,Y_branch_Code  )
 select customer_Num   , Branch_Code 
 from Table
 where Channel_Id   = 'Y';


insert into Result_table (customer_Num  ,L_Branch_Code)
select customer_Num   , Branch_Code 
from Table
where Channel_Id  = 'L';
1

You're looking for the PIVOT clause. You can see a working example in this db<>fiddle.

The query is:

SELECT customer_num
  ,p.I AS I_Branch_Code
  ,p.Y AS Y_Branch_Code
  ,p.L AS L_Branch_Code
FROM
(
  SELECT customer_num
    ,Channel_Id
    ,Branch_code
  FROM [table]
)s
PIVOT
(
  MAX(Branch_Code) FOR Channel_Id IN ([I], [Y], [L])
) p
| improve this answer | |

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