Re-format table, placing multiple column headers as rows-CodePudding

I have a table of fishing catches, showing number of fish and total kg, for all the fishing days. Current format of the data is showing as below

In the other reference table is a list of all the official fish species with codes and names.

How can I re-format the first table so the rows are repeated for each day showing a certain species with the corresponding total catches and kgs in a row. So instead of the species kg and n having their different columns, I would have them in rows while there is only one n and kg column. I am thinking of looping through the list of all species and based on the numbers it will duplicate the rows in a way with the right values of n and kg of the species in the rows. This is the final format I need. My database is SQL Server.

CodePudding user response：

You may use a union query here:

SELECT Day, 'Albacore' AS Species, ALB_n AS n, ALB_kg AS kg FROM yourTable
UNION ALL
SELECT Day, 'Big eye tuna', BET_n, BET_kg FROM yourTable
UNION ALL
SELECT Day, 'Sword fish', SWO_n, SWO_kg FROM yourTable
ORDER BY Day, Species;

CodePudding user response：

You can also use a cross apply here, e.g.:

/*
 * Data setup...
 */
create table dbo.Source (
  Day int,
  ALB_n int,
  ALB_kg int,
  BET_n int,
  BET_kg int,
  SWO_n int,
  SWO_kg int
);
insert dbo.Source (Day, ALB_n, ALB_kg, BET_n, BET_kg, SWO_n, SWO_kg) values
  (1, 10, 120, 4, 60, 2, 55),
  (2, 15, 170, 8, 100, 1, 30);
create table dbo.Species (
  Sp_id int,
  Sp_name nvarchar(20)
);
insert dbo.Species (Sp_id, Sp_name) values
  (1, N'Albacore'),
  (2, N'Big eye tuna'),
  (3, N'Sword fish');
/*
 * Unpivot data using cross apply...
 */
select Day, Sp_name as Species, n, kg
from dbo.Source
cross apply dbo.Species
cross apply (
  select
    case
      when Sp_name=N'Albacore' then ALB_n
      when Sp_name=N'Big eye tuna' then BET_n
      when Sp_name=N'Sword fish' then SWO_n
    else null end as n,
    case
      when Sp_name=N'Albacore' then ALB_kg
      when Sp_name=N'Big eye tuna' then BET_kg
      when Sp_name=N'Sword fish' then SWO_kg
    else null end as kg
) unpivotted (n, kg);