title: Combine two pivot tables SQL Server
body: <p>I am trying to cross join two pivot tables.</p>
  
  <p>--First Pivot table query gets columns of drug_names and produces the row value
  --of the drug_id.</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX)
  
  Select @cols = STUFF((SELECT ',' + QUOTENAME(drug) 
                      from _app_drugs
                      group by drug, drug_id,order_number
                      order by order_number
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  set @query = N'SELECT ' + @cols + N' from 
               (
                  select drug_id, drug
                  from _app_drugs
              ) x
              pivot 
              (
                  max(drug_id)
                  for drug in (' + @cols + N')
              ) p'
  
  exec sp_executesql @query;
  
  --The second pivot table simply gets the signature_labels and displays the label name in the column and displays the label_id as the row value.
  
  DECLARE @cols AS NVARCHAR(MAX),@scols as NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX)
  
  Select @cols = STUFF((SELECT ',' + QUOTENAME(signature_label) 
                      from _app_signature_labels
                      WHERE _app_signature_labels.isactive=1
                      group by signature_label_id, signature_label,ordernumber
                      order by ordernumber
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'')
  
  set @query = N'SELECT ' + @cols + N' from 
               (
                  select signature_label_id,signature_label
                  from _app_signature_labels              
              ) x
              pivot 
              (
                  max(signature_label_id)
                  for signature_label in (' + @cols + N')
              ) p'
  
  exec sp_executesql @query;
  </code></pre>
  
  <p>Now I just need to know how to combine these two pivot tables as one table... they don't have a common field and don't need one.</p>
  
  <p>Can anybody help me with this one?
  Thank you</p>