How to do this SELECT (SQL Server 2008)?

Navigation
1

To be honest, I do not even know how to start my question. So I'll first put the information I have.

I have the following structure in my database.

I need to do a SELECT that returns me the following:

How can I achieve this?

    
asked by anonymous 08.01.2015 / 21:09

1 answer

1

Use 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

Output: 

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

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

See working in SQLFiddle

    
09.01.2015 / 03:40
How to insert the name of the files loaded in the database How do I keep an item in a static controller part of the page?