Wsrtjtyk

I would like to ask you if there's efficient way to write a query to retrieve the data from tables provided below and display only the columns and values I want. I already wrote two queries however the first one is a bit slow and in the second I got more columns than I want.

So here are my three tables:

And here's the output I want:

Output table should display monthly values from Jan till Dec.
In the real database Table A has ~40k rows, Table B has 20 rows and Table C has ~1 million rows.
Below you can see part of a query which I wrote and which generate desired output, however when I ran it for real DB it takes ~20-30 seconds:

  SELECT TableA.Id

    ,TableA.Title

    ,TableA.Description

    ,TableB.Title

    ,f1.[Value] as TableC_JanuaryValue

    ,f2.[Value] as TableC_FebruaryValue

    ....

    FROM <tablename>

INNER JOIN TableB ON TableB.Id = TableA.TableB_FK_Id

INNER JOIN TableC as f1 ON (f1.TableA_FK_Id = TableA.Id AND f1.[Year] = 2018 AND f1.[Month] = 1)

INNER JOIN TableC as f2 ON (f2.TableA_FK_Id = TableA.Id AND f2.[Year] = 2018 AND f2.[Month] = 2)

.....

I was trying to write it differently and make it faster and I managed to get it down to 2-3 seconds, however the output is not exactly what I want.
Here's the second query:

  SELECT TableA.Id

    ,TableA.Title

    ,TableA.Description

    ,TableB.Title

    ,count(case when TableC.Value = 'V1' and TableC.[Month] = 1 then TableC.Id end) as JAN_IsV1

    ,count(case when TableC.Value = 'V2' and TableC.[Month] = 1 then TableC.Id end) as JAN_IsV2

    ,count(case when TableC.Value = 'V3' and TableC.[Month] = 1 then TableC.Id end) as JAN_IsV3

    ,count(case when TableC.Value = 'V1' and TableC.[Month] = 2 then TableC.Id end) as FEB_IsV1

    ,count(case when TableC.Value = 'V2' and TableC.[Month] = 2 then TableC.Id end) as FEB_IsV2

    ,count(case when TableC.Value = 'V3' and TableC.[Month] = 2 then TableC.Id end) as FEB_IsV3

    ....

    FROM <tablename>

INNER JOIN TableB ON TableB.Id = TableA.TableB_FK_Id

INNER JOIN TableC ON TableA.Id = TableC.TAbleA_FK_Id

where TableC.[Year] = 2018

group by TableA.Id, TableA.Title, TableA.Description, TableB.Title

And here's what I get:

I know that its very close to the result I want, however its not exactly the same :(

Just do the pivoting on TableC before you join it to the others. And use MAX(CASE) instead of COUNT(CASE)

SELECT

    TableA.Id

    ,TableA.Title

    ,TableA.Description

    ,TableB.Title

    ,TableC.*

FROM

    TableA

INNER JOIN

    TableB

        ON TableB.Id = TableA.TableB_FK_Id

INNER JOIN

(

    SELECT

        TableA_FK_Id,

        MAX(CASE WHEN [Month] = 1 THEN Value END) AS JAN,

        MAX(CASE WHEN [Month] = 2 THEN Value END) AS FEB, 

        MAX(CASE WHEN [Month] = 3 THEN Value END) AS MAR, 

        ...

    FROM

        TableC

    WHERE

        [YEAR] = 2018

    GROUP BY

        TableA_FK_Id

)

    TableC

        ON TableA.Id = TableC.TableA_FK_Id

 SELECT 

       tablea.Id AS TableA_Id,  

       tablea.Title AS TableA_Title, 

       tablea.Description AS TableA_Description,

       (SELECT tableb.Title FROM tableb WHERE tableb.Id = TableA.TableB_FK_Id) AS TableB_Id,

       (SELECT tablec.Value FROM tablec WHERE tablea.Id = TableC.TableA_FK_Id AND tablec.Month = 1) AS TableC_JanValue,

       (SELECT tablec.Value FROM tablec WHERE tablea.Id = TableC.TableA_FK_Id  AND tablec.Month = 2) AS TableC_FebValue

FROM tablea;

this one also u can try..!! :)