Home     |     .Net Programming    |     cSharp Home    |     Sql Server Home    |     Javascript / Client Side Development     |     Ajax Programming

Ruby on Rails Development     |     Perl Programming     |     C Programming Language     |     C++ Programming     |     IT Jobs

Python Programming Language     |     Laptop Suggestions?    |     TCL Scripting     |     Fortran Programming     |     Scheme Programming Language


 
 
Cervo Technologies
The Right Source to Outsource

MS Dynamics CRM 3.0

Sql Server Programming

Creating a Summary Table - Pivot ?


I have a table with the following structure.  There is a unique ID for
each column:

Unique_ID   Grade1   Grade2   Grade3   Grade4
1                  A+          A           B            A
2                 C             A+        C            D
etc.

Basically, there are rows of grades per unique ID.

I need to create a summary table based on the first table.  For each
grade, I want a count of each A+ grade, each A grade, each B grade,
etc

For example:

Grade_Name   A+    A    B   C    D
Grade1           1       0    0    1    0
Grade2           1       1    0    0    0

I've tried many things and am a little confused!  Any help would be
appreciated!

Thanks,
AS

Basically you need to unpivot the table first to a normalized form and then
to pivot to the summary that you need. Here is one way to do this:

-- Unpivot with a view
CREATE VIEW GradesNorm (Grade_Name, Grade)
AS
SELECT 'Grade1', Grade1 FROM Grades
UNION ALL
SELECT 'Grade2', Grade2 FROM Grades
UNION ALL
SELECT 'Grade3', Grade3 FROM Grades
UNION ALL
SELECT 'Grade4', Grade4 FROM Grades
GO

-- Pivot to summarize
SELECT Grade_Name,
           SUM(CASE WHEN Grade = 'A+' Then 1 ELSE 0 END) AS 'A+',
           SUM(CASE WHEN Grade = 'A' Then 1 ELSE 0 END) AS 'A',
           SUM(CASE WHEN Grade = 'B' Then 1 ELSE 0 END) AS 'B',
           SUM(CASE WHEN Grade = 'C' Then 1 ELSE 0 END) AS 'C',
           SUM(CASE WHEN Grade = 'D' Then 1 ELSE 0 END) AS 'D'
FROM GradesNorm
GROUP BY Grade_Name

If you are on SQL Server 2005 you can look up UNPIVOT and PIVOT.

HTH,

Plamen Ratchev
http://www.SQLStudio.com

-----------------------------------------------Reply-----------------------------------------------

BTW, I did this with a view just to illustrate the process, you can do it in
a single query like this:

SELECT Grade_Name,
           SUM(CASE WHEN Grade = 'A+' Then 1 ELSE 0 END) AS 'A+',
           SUM(CASE WHEN Grade = 'A' Then 1 ELSE 0 END) AS 'A',
           SUM(CASE WHEN Grade = 'B' Then 1 ELSE 0 END) AS 'B',
           SUM(CASE WHEN Grade = 'C' Then 1 ELSE 0 END) AS 'C',
           SUM(CASE WHEN Grade = 'D' Then 1 ELSE 0 END) AS 'D'
FROM (
          SELECT 'Grade1' AS Grade_Name, Grade1 AS Grade FROM Grades
          UNION ALL
          SELECT 'Grade2', Grade2 FROM Grades
          UNION ALL
          SELECT 'Grade3', Grade3 FROM Grades
          UNION ALL
          SELECT 'Grade4', Grade4 FROM Grades) AS G
GROUP BY Grade_Name

Plamen Ratchev
http://www.SQLStudio.com

-----------------------------------------------Reply-----------------------------------------------

Thanks for the great suggestions. It's taking me a while to figure
out
the differences between pivot and unpivot.  My final solution:

SELECT  Grade_Name, Sum(CASE WHEN Grades = 'A+' THEN 1 ELSE 0 END) AS
APlus
, Sum(CASE WHEN Grades = 'A' THEN 1 ELSE 0 END) AS A
, Sum(CASE WHEN Grades = 'B' THEN 1 ELSE 0 END) AS B
, Sum(CASE WHEN Grades = 'C' THEN 1 ELSE 0 END) AS C
, Sum(CASE WHEN Grades = 'D' THEN 1 ELSE 0 END) AS D
, Sum(CASE WHEN Grades = 'n/a' THEN 1 ELSE 0 END) AS NA
FROM
   (SELECT *
   FROM GradesTable) p
UNPIVOT
   (Grades FOR Grade_Name IN
      ([Grade1], [Grade2], [Grade3], [Grade4])

)AS unpvt
Group By Grade_Name

Add to del.icio.us | Digg this | Stumble it | Powered by Megasolutions Inc