Join execute query to table

Question

I have 2 tables :

and

.

Then I have this code which creates dynamic pivot table from 1st table and joins it with 2nd table.

DECLARE @cols AS NVARCHAR( MAX ),                                      
        @query AS NVARCHAR( MAX )                                         
        SET @cols = STUFF( ( SELECT ', ' + QUOTENAME( Nazev )
                    FROM Tab1
                    FOR XML PATH( '' ),
                    TYPE
                  ).value( '.', 'NVARCHAR( MAX )' ),
                  1,
                  1,
                  ''
                )                                                         
    SET @query = 'SELECT ZamestnanecID,' + @cols +
         'FROM ( SELECT ZamestnanecID,
                        Tab1.Nazev AS Nazev2,
                        tab2.[castka]
                 FROM Tab1
                 JOIN Tab2 On Tab2.typ = Tab1.Id
               ) AS prePivot
          PIVOT
          (
              SUM( castka )
              FOR Nazev2 IN (' + @cols + ')
          ) p'                                                
         execute(@query)

And i need to somehow store the executed query so i can join it with tab3 by ZamestnanecId.It contains these columns: Jméno, Příjmení, ZaměstnanecId. (i can't post image because of image limit)

Any ideas how to do it ? It would be best to use executed query as "from" parameter in select but it is not necessary

Answer 1

You cannot store results but can instead continue using dynamic query like below

DECLARE 
    @cols AS NVARCHAR( MAX ),                                      
    @query AS NVARCHAR( MAX )                                         
SET @cols = STUFF( ( SELECT ', ' + QUOTENAME( Nazev )
                FROM Tab1
                FOR XML PATH( '' ),
                TYPE
              ).value( '.', 'NVARCHAR( MAX )' ),
              1,
              1,
              ''
            )                                                         
SET @query = 'SELECT * FROM tab3 t3 JOIN '+
     '( SELECT ZamestnanecID,' + @cols +
     ' FROM ( SELECT ZamestnanecID,
                    Tab1.Nazev AS Nazev2,
                    tab2.[castka]
             FROM Tab1
             JOIN Tab2 On Tab2.typ = Tab1.Id
           ) AS prePivot
      PIVOT
      (
          SUM( castka )
          FOR Nazev2 IN (' + @cols + ')
      ) p ) t '+
      ' on t.ZamestnanecId=t3.ZamestnanecId'
     execute(@query)