title: How do I create this pivot SQL query?
body: <p>I have the following tables in our SQL Server 2012 instance:</p>
  
  <p><strong>tblASSETS</strong></p>
  
  <pre><code>------------------------------------
  | ASSETID | ASSETTYPE | NAME       | 
  |---------|-----------|------------|
  | 1       | A         | Printer A  |
  | 2       | A         | Printer B  |
  | 3       | A         | Printer C  |
  | 4       | B         | Laptop A   |
  ------------------------------------
  </code></pre>
  
  <p><strong>tblASSETTYPES</strong></p>
  
  <pre><code>--------------------------------------
  | ASSETTYPE | TYPENAME | ICON        |
  |-----------|----------|-------------|
  | A         | Printer  | Printer.png |
  | B         | Laptop   | Laptop.png  |
  --------------------------------------
  </code></pre>
  
  <p><strong>tblASSETCUSTOM</strong></p>
  
  <pre><code>-------------------------------------------------------------
  | CUSTOMID | ASSETID | MAKE | MODEL          | PRINTEDPAGES |
  |----------|---------|------|----------------|--------------|
  | 1        | 1       | HP   | Laserjet 4v    | 530          |
  | 2        | 2       | HP   | Laserjet 4v    | 10000        |
  | 3        | 3       | HP   | Officejet 1050 | NULL         |
  | 4        | 4       | HP   | Probook 430 G3 | NULL         |
  -------------------------------------------------------------
  </code></pre>
  
  <p><strong>tblOIDDATA</strong></p>
  
  <pre><code>---------------------------------------------
  | OIDDATAID | ASSETID | LABEL        | DATA |
  |-----------|---------|--------------|------|
  | 1         | 1       | Black copies | 430  |
  | 2         | 1       | Color copies | 110  |
  | 3         | 2       | Black copies | 5300 |
  | 4         | 2       | Scans        | 450  |
  ---------------------------------------------
  </code></pre>
  
  <p>I want to build a query which returns all printers and all their details as columns. I already created this QUERY:</p>
  
  <pre><code>DECLARE @cols AS NVARCHAR(MAX),
      @query  AS NVARCHAR(MAX)
  
  select @cols = STUFF((SELECT ',' + QUOTENAME(label) 
                      from tblOIDData
                      group by label
                      order by label
              FOR XML PATH(''), TYPE
              ).value('.', 'NVARCHAR(MAX)') 
          ,1,1,'') 
  
  set @query = 'Select * from (   Select Top 1000000 tAT.icon As icon, tat.typename as [ASSET TYPE] ,tA.Name as [ASSET NAME], tac.Model as [DEVICE MODEL], snmp.label as label, TRY_CONVERT(INT, TRY_CONVERT(NVARCHAR(100), snmp.data)) as PageCount, TA.Printepages as [PRINTED PAGES]   from tblAssets as tA,       tblAssetCustom as tAC,          tsysAssetTypes as tAT,      tblOIDData as SNMP  where tA.AssetType = tAT.AssetType      AND tAT.Typename = ''Printer''      AND tAC.AssetID = tA.AssetID        AND snmp.AssetID = tA.AssetID        ) as s PIVOT (     sum(PageCount)  for [LABEL] IN (' + @cols + ') ) AS pvt'
  
  execute(@query);
  </code></pre>
  
  <p>This almost give the desired result. The only thing I'm facing is that ASSETID 3 (Printer C) is not in the result. Probably because it is not in the tblOIDData table.</p>
  
  <p>How can I include this Asset in my results also?</p>