title: Rows To Columns in SQL SERVER USING PIVOT Command (Replacing NULL Values To 0 & Display SUM Of ALL VALUES)
body: <p>I want to display rows to columns in Sql Server. I have seen the other questions but those columns are hardcoded in the pivot  but my columns will be dynamic.  What I have achieved till now. As shown in the screenshot I am able to convert the rows into columns but few things I am not able to accomplish.. Need your guyz help</p>
  
  <ol>
  <li>Replacing NULL To 0 in All the Columns</li>
  <li>Need to Add 1 more column which will show the sum of all Columns except the companyID</li>
  </ol>
  
  <p>My SQL code:</p>
  
  <pre><code>DECLARE @Columns VARCHAR(MAX)
  DECLARE @Convert VARCHAR(MAX)
  
  SELECT  @Columns = STUFF((
             SELECT '],[' + ErrClassfn
               from ArchimedesTables.dbo.PM_ErrClassificationSetup
              WHERE CONVERT(VARCHAR(10), ISNULL(EndDate, GETDATE()), 101) 
                            Between CONVERT(VARCHAR(10), GETDATE(), 101)
                            AND     CONVERT(VARCHAR(10), GETDATE(), 101)
              ORDER BY '],[' + CONVERT(VARCHAR(MAX), ID) ASC
                FOR
                    XML PATH('')
             ), 1, 2, '') + ']'
  
  SET @Convert = 'SELECT  * INTO #mynewTable FROM
  (
      SELECT COUNT(WQ.ErrClassfnID) as ErrorCount, UPPER(WQ.CompanyID) as CompanyID, 
             PME.ErrClassfn as ErrorName
        FROM Version25.dbo.WF_Quality AS WQ
        LEFT JOIN ArchimedesTables.dbo.PM_ErrClassificationSetup as PME
             ON WQ.ErrClassfnID = PME.ID
       GROUP BY
            UPPER(CompanyID), ErrClassfn
  ) Quality PIVOT ( SUM(ErrorCount)  For ErrorName IN (' + @Columns
      + ')) as PivotTable  SeLeCt * FROM #mynewTable'
  
  EXEC(@Convert)
  </code></pre>
  
  <p><img src="https://i.stack.imgur.com/JaTj7.gif" alt="enter image description here"></p>