I have two tables and Table1 has all the values which I need to fill in Table2. How can I get column name in row with their values?

Table1:

Table2:

CodePudding user response：

In SQL Server this can easily be done by PIVOT function, and using INSERT INTO SELECT.

Try:

insert into table2 (serial_no,
                    Feild_name,
                    Feild_value
                    )
select s_no as serial_no,
       Feild_name,
       Feild_value
FROM table1
unpivot
(
  Feild_value
  for Feild_name in (function_col,Head,Subhead,Via)
) unpiv;  

Demo

CodePudding user response：

We can run 4 queries for the 4 categories and then join them with UNION ALL.
NB this method will not work if we add new categories.
This is coded in SQL server but should work for most RDBMS.

create table t(
s_no  int,
ID    char(4),
Functionn varchar(25),
Head      varchar(25),
Subhead       varchar(25),
Via       varchar(25),
transferID int);
insert into t values
(1,'A529','Service','Cost','Cost','online',456789),
(2,'A432','Part change','cost','Cost','Cash',1000);
GO

2 rows affected

select s_no serial_number, 'Function' "Field Name", Functionn "Field Value" from t
union all
select s_no serial_number, 'Head' ,Head from t
union all
select s_no serial_number, 'Sub-Head' ,Subhead from t
union all
select s_no serial_number, 'Via' ,Via from t
order by s_no,"Field Name"
GO

serial_number | Field Name | Field Value
------------: | :--------- | :----------
            1 | Function   | Service    
            1 | Head       | Cost       
            1 | Sub-Head   | Cost       
            1 | Via        | online     
            2 | Function   | Part change
            2 | Head       | cost       
            2 | Sub-Head   | Cost       
            2 | Via        | Cash       

db<>fiddle here