troubleshooting Question

Convert sql 2000 query to sql server 2005 crosstab dynamic query?

Avatar of Saroj13
Saroj13 asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
6 Comments1 Solution341 ViewsLast Modified:
Hi,

Please find my storedprocedure:

i want to display report as
defectid  defecttype  location1 location2  location3   etc   Total
21             type1            0              6              0                            6
12              type2          1               8                9                          18


CREATE PROCEDURE [dbo].[usp_GetDefectDetailsByLocation]
      @Month int,
      @Year int
AS
BEGIN
      DECLARE @SqlString NVARCHAR(4000)
      DECLARE @tempsql NVARCHAR(4000)
      DECLARE @SqlStringCur NVARCHAR(4000)
      DECLARE @col NVARCHAR(100)
      SET @SqlString = ''
      SET NOCOUNT ON;


      SELECT RTRIM([LocationName]) AS Location INTO #temptbl_Cursor2  
            FROM Locations
            ORDER BY [LocationName]


      DECLARE xcursor CURSOR
      FOR
      SELECT [LocationName] FROM #temptbl_Cursor2
      OPEN xcursor      
      FETCH NEXT FROM xcursor INTO @Col      
      WHILE @@Fetch_Status = 0
      BEGIN
              SET @Sqlstring = @Sqlstring + ', '
              SET @tempsql = @Sqlstring +  ' SUM( CASE Locationname WHEN  ' + CHAR(39) + RTRIM(@Col) + CHAR(39) + ' THEN TOTAL ELSE 0 END ) AS [' + RTRIM( @Col) + ']'
              SET @Sqlstring = @tempsql
             FETCH NEXT FROM xcursor INTO @Col
      END
      CLOSE xcursor
      DEALLOCATE xcursor  
 

      SELECT D.DefectID,
                  RTRIM(D.DefectName) AS DefectName,
               L.LocationName AS LocationName,
               COUNT(*) AS TOTAL INTO #RESULTS
            FROM FFEEDBACK DF
            INNER JOIN Defect D ON D.DefectID = DF.DefectID
            INNER JOIN Locations L ON L.LocationID = DF.LocationID
            WHERE MONTH(DF.DateSubmitted) = @Month
            AND YEAR(DF.DateSubmitted) = @Year
            GROUP BY D.DefectID, D.DefectName, L.LocationName

     
            SET @tempsql = 'SELECT DefectID, DefectName '   + @Sqlstring + '  INTO ##DEFECTTOTALS FROM #RESULTS  GROUP BY DefectID, DefectName'
       SET @Sqlstring = @tempsql

      EXECUTE sp_executesql @Sqlstr
     
     
     
      INSERT INTO ##DEFECTTOTALS
      SELECT DefectID, DefectName, 0,0,0,0
            FROM Defect
            WHERE DefectID NOT IN (SELECT DefectID FROM ##DEFECTTOTALS)
           


      INSERT INTO ##DEFECTTOTALS
      SELECT 999, 'Total number of Defects', SUM([Columbus]),SUM([Texas]),SUM([NY]),SUM(NJ)
            FROM ##DEFECTTOTALS

      SELECT S.*,  '~/DefectDescriptions.aspx' AS NavigateUrl,
                  (S.[Columbus] + S.[Texas] + S.NY + S.[NJ]) AS TOTALS,
                  CASE S.DefectName
                        WHEN 'Other' THEN 2
                        WHEN 'Total number of Defects' THEN 3
                        ELSE 1
                  END
                  AS DisplayOrderby
            FROM ##DEFECTTOTALS S
            ORDER BY DisplayOrderby, S.DefectName

      DROP TABLE #temptbl_Cursor2
       DROP Table #RESULTS
       DROP Table ##DEFECTTOTALS
END
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 6 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros