Separate Columns in SQL Row Numbers

Question

to with a problem for SQL Server.

I have an event and value table and I make a Join with another table that has several columns, the amount can be marked as 1 for active and 0 inactive.

As you can see in the image line 1 has the RD8 , RD10 and RD21 marked, wanted the value of 97.24 to be demonstrated in lines but only for the RD’s marked.

She’d have to stay that way.

SQL de Criação

CREATE TABLE PFFINANC
(
    CODCOLIGADA       INT  NOT NULL,
    CHAPA             VARCHAR (4) NOT NULL,
    ANOCOMP           SMALLINT NOT NULL,
    MESCOMP           SMALLINT NOT NULL,
    CODEVENTO         VARCHAR (4) NOT NULL,
    VALOR             RMDVALOR2 NULL,
)


    

CREATE TABLE dbo.ZMDCUSTO_PESSOAL
(
    CODCOLIGADA   INT NOT NULL,
    CODEVENTO     VARCHAR (4) NOT NULL,
    RD1           BIT NULL,
    RD2           BIT NULL,
    RD3           BIT NULL,
    RD4           BIT NULL,
    RD5           BIT NULL,
    RD6           BIT NULL,
    RD7           BIT NULL,
    RD8           BIT NULL,
    RD9           BIT NULL,
    RD10          BIT NULL,
    RD11          BIT NULL,
    RD12          BIT NULL,
    RD13          BIT NULL,
    RD14          BIT NULL,
    RD15          BIT NULL,
    RD16          BIT NULL,
    RD17          BIT NULL,
    RD18          BIT NULL,
    RD19          BIT NULL,
    RD20          BIT NULL,
    RD21          BIT NULL,
    RD22          BIT NULL,
    RD23          BIT NULL,
    RD24          BIT NULL,
    RD25          BIT NULL,
    RD26          BIT NULL,
    RD27          BIT NULL,
    RD28          BIT NULL,
    RD29          BIT NULL,
    RD30          BIT NULL,
    RECCREATEDBY  VARCHAR (50) NULL,
    RECCREATEDON  DATETIME NULL,
    RECMODIFIEDBY VARCHAR (50) NULL,
    RECMODIFIEDON DATETIME NULL,
    PRIMARY KEY (CODCOLIGADA, CODEVENTO)
)

Answer 1

For this you can use the upivot.

You can use PIVOT and UNPIVOT relational operators to change an expression with table value for another table. PIVOT rotates a expression with table value when transforming the exclusive values of a column in the multi-column expression in the output. And PIVOT executes aggregations where they are required at any column values remnants that are desired in the final output. UNPIVOT executes the operation opposite to PIVOT, transforming the columns of an expression with table value in column values.

Source: Microsoft Pivot and Unpivot documentation

Functioning

To make the unpivot is very easy. Do first the SQL with normal column data, and columns we want to turn into rows. This part I put the alias as base.

Then just use the command unpivot, thus:

unpivot (nomedacoluna for qualquernome in (colunas))

• nomedacolune - Column name to use on SQL leading;
• any name - Any name here, as it only serves for the command;
• columns - The columns you want to turn into rows

The unpivot only turns columns into rows if it has value. So I used the command case to already leave the correct and expected result.

Example

Structure

create table PFFINANC (
  Grupo varchar(50),
  CodEvento varchar(4),    
  Descricao varchar(100),
  Valor decimal(15, 2)
);
 
insert into PFFINANC values ('03 - Motoristas', '0003', 'Salário Família', 97.24);
insert into PFFINANC values ('05 - Manutenção', '0005', 'Salário Família', 48.62);

CREATE TABLE ZMDCUSTO_PESSOAL
(
  CodEvento varchar(4),   
  RD1 bit,
  RD2 bit,
  RD3 bit,
  RD4 bit
);

insert into ZMDCUSTO_PESSOAL values ('0003', 0, 0, 1, 1)
insert into ZMDCUSTO_PESSOAL values ('0005', 1, 0, 1, 1)

SQL

select 
  Grupo,
  CodEvento,
  Descricao,
  Valor,
  rd
from
  (
    select
      Grupo,
      a.CodEvento,
      Descricao,
      Valor,    
      case when RD1 = 1 then 'RD1' else null end as RD1,
      case when RD2 = 1 then 'RD2' else null end as RD2,
      case when RD3 = 1 then 'RD3' else null end as RD3,
      case when RD4 = 1 then 'RD4' else null end as RD4            
    from
      PFFINANC a
      inner join ZMDCUSTO_PESSOAL b on b.CodEvento = a.CodEvento
  ) base
  unpivot (rd for colunasrd in (RD1, RD2, RD3, RD4)) as linhas
  

Example in Sqlfiddle

Another example 01

Another example 02

Other example 03

Observing: I did only with some columns to post the example so not putting all fields, and also put fields that I thought were missing in the tables you used.