How to do this SELECT (SQL Server 2008)?

Asked

Viewed 163 times

1

to be quite honest I don’t even know how to start my question. So I’ll first put the information I have.

I have the following structure in my database.

Tabela A

Tabela B

And I need to make a SELECT that returns to me the following:

Saída

How can I get this result?

sql sql-server select sql-server-2008

  • The question is not complete. Information is lacking for your understanding.

  • If the idea is to open each column (3 adults in adult 1, 2 and 3) I think it would be the case of more than one select. I would write a logic with TSQL or the solution programming language.

  • @P8Q what information you need to help me?

  • @Anthonyaccioly what would this logic look like? I am without ideas on how to solve this problem. I am using SQL Server 2008

  • @Leonardoribeirodeaguiar here I can not see what is below "I have" and "I need". I saw that there are two images, but they do not appear at least here for me.

  • 1

    Unpivot https://technet.microsoft.com/pt-br/library/ms177410(v=sql.105). aspx

Show 1 more comment

1 answer

1

Utilize INNER JOIN with any table of numbers:

CREATE TABLE NUMEROS (n INT);
INSERT INTO NUMEROS VALUES (1),(2),(3),(4),(5),(6);

SELECT 
  'Adulto ' + CAST(NUMEROS.n AS VARCHAR) AS PESSOA ,
  TabelaB.ValorAdulto AS VALOR
FROM TabelaA
INNER JOIN NUMEROS
  ON TabelaA.QtdAdulto >= NUMEROS.n
INNER JOIN TabelaB
  ON TabelaA.IDTabelaB = TabelaB.IDTabelaB

Exit:

|   PESSOA  | VALOR |
|-----------|-------|
|  Adulto 1 |   200 |
|  Adulto 2 |   200 |
|  Adulto 3 |   200 |

Now just use the UNION ALL for the columns QtdCrianca,QtdBebe and QtdSenior.

See working on Sqlfiddle

  • 1

    Using an UNPIVOT may also help http://technet.microsoft.com/pt-br/library/ms177410%28v=sql.105%29.aspx

Browser other questions tagged sql sql-server select sql-server-2008

You are not signed in. Login or sign up in order to post.