SQL: Custom Subqueries

Question

I have an SQL query (MSSQLSERVER) where I add columns to the result set using subqueries:

SELECT P.name, 
(select count(*) from cars C where C.type = 'sports') AS sportscars,
(select count(*) from cars C where C.type = 'family') AS familycars,
(select count(*) from cars C where C.type = 'business') AS businesscars
FROM people P
WHERE P.id = 1;

The above query is just a test that is a little nonsense, but it works reasonably well, as I think. The query I'm actually working on contains many complex tables that only distract from the problem.

In the above example, each entry in the "people" table also contains three additional columns: "wantsSportsport", "wantsFamilycar" and "wantsBusinesscar". Now what I want to do is only make a subquery of each additional column, if the corresponding field "wants ....." in the people table is set to "true". In other words, I only want to make the first sub-collection if P.wantsSportscar is set to true for that particular person. The second and third sub-elections should work in a similar way.

So, the way this query should work is that it shows the name of a particular person and the number of available models for the types of cars that he wants to own. It may be worth noting that my final set of results will always contain only one record, namely one specific user.

It is important that if a person is not interested in a particular type of car, then the column for this type will not be included in the final result set. An example to make this clear:

If person A wants a sports car and a family car, the result will include the columns “name”, “sports cars” and “family cars”.

If person B wants a business car, the result will include the columns "name" and "business car".

IF, CASE EXISTS, . - , ? , .

Answer 1

8, 8 ( ).

.

:

SELECT  P.name, 
        CASE WHEN wantssport = 1 THEN (select count(*) from cars C where C.type = 'sports') ELSE NULL END AS sportscars,
        CASE WHEN wantsfamily = 1 THEN (select count(*) from cars C where C.type = 'family') ELSE NULL END AS familycars,
        CASE WHEN wantsbusiness = 1 THEN (select count(*) from cars C where C.type = 'business') ELSE NULL END AS businesscars
FROM    people P
WHERE   P.id = 1

NULL , , NULL .

, relations.

: " , - ".

.

: NULL, SQL, , , .

Answer 2

, , Oracle, . , , , - . , , X , X .

, MS SQL Server, , - SQL, , .

Answer 3

, , temp, PIVOT ( ).

Answer 4

, , . , :

SQL. NULL ZERO.

, , PIVOTing .

Answer 5

, , . - , - GROUP BY, - PIVOT.

-, . . "wantports", "wantfamily", "wantbusiness" , . , , "peoplewant", , . , , , .

. GROUP BY. , .

SELECT 
    p.name, 
    c.type, 
    c.count(*) as carcount
FROM people p, 
   INNER JOIN peoplewant pw ON p.id = pw.personid 
   INNER JOIN cars c on pw.cartype = c.type
WHERE
   p.id = 1
GROUP BY 
   p.name,
   c.type

() , , , , .

, PIVOT. PIVOT , , , , . PIVOT, - , PIVOT.

, , , , PIVOT , , .

Answer 6

I just stumbled upon this post through a search on Google, so I understand that I was a little late for this party, but ... I'm sure it is really possible ... however, I would not suggest actually doing it this way, because it is usually considered very bad (tm).

Dynamic SQL is your answer.

Before I tell you how to do this, I want a preface to this, Dynamic SQL is a very dangerous thing if you do not deactivate your input from the application.

So, proceed with caution:

declare @sqlToExecute nvarchar(max);
declare @includeSportsCars bit;
declare @includeFamilyCars bit;
declare @includeBusinessCars bit;

set @includeBusinessCars = 1
set @includeFamilyCars = 1
set @includeSportsCars  = 1

set @sqlToExecute = 'SELECT P.name '

if @includeSportsCars = 1 
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''sports'') AS sportscars, ';
if @includeFamilyCars = 1
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''family'') AS familycars, ';
if @includeBusinessCars = 1
    set @sqlToExecute = @sqlToExecute + '(select count(*) from cars C where C.type = ''business'') AS businesscars '

set @sqlToExecute = @sqlToExecute + ' FROM people P WHERE P.id = 1;';

exec(@sqlToExecute)