CODEWITHSUNDEEP
sqlsql-server-2008-r2
SkeetJon
SkeetJon

Reputation: 1491

SQL Server count entries in one table against another table

I have two temporary tables in SQL Server 2008

Table 1, results_types

------------
| Result_1 |
| Result_2 |
| Result_3 |
| Result_N |
------------

Table 2, all_results

--------------------
| Run_A | Result_1 |
| Run_A | Result_2 |
| Run_B | Result_3 |
| Run_B | Result_3 |
| Run_B | Result_3 |
| Run_B | Result_3 |
| Run_C | Result_2 |
--------------------

How can I make a query that returns a summary which counts each entry in *results_types* without iterating through *results_types* please. Thanks in advance

-----------------------------------------------------
| 'Run' | Result_1 | Result_2 | Result_3 | Result_N |
-----------------------------------------------------
| Run_A | 1        | 2        | 0        | 0        |
| Run_B | 0        | 0        | 4        | 0        |
| Run_C | 0        | 2        | 0        | 0        |
-----------------------------------------------------

Upvotes: 0

Views: 79

Answers (1)

GarethD
GarethD

Reputation: 69759

You can use the SQL-server Pivot function and dynamic SQL:

DECLARE @Cols NVARCHAR(MAX) = STUFF((   SELECT  ',' + Result_Type
                                        FROM    Result_Types
                                        FOR XML PATH(''), TYPE
                                    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

DECLARE @SQL NVARCHAR(MAX) = '  SELECT  *
                                FROM    all_results
                                        PIVOT
                                        (   COUNT(Result_Type)
                                            FOR Result_Type IN (' + @Cols + ')
                                        ) pvt;'
EXECUTE SP_EXECUTESQL @SQL;

Example on SQL Fiddle

Upvotes: 2

Related Questions