Rotates A Table By Using POVIT & UNPOVIT

POVIT : Rotates A Table By Turning The Unique Values From One Columns Into Multiple Columns In The Output.

 

Syntax for PIVOT

SELECT <non-pivoted column>,

[first pivoted column] AS <column name>,

[second pivoted column] AS <column name>,

...

[last pivoted column] AS <column name>

FROM

(<SELECT query that produces the data>)

AS <alias for the source query>

PIVOT

(

<aggregation function>(<column being aggregated>)

FOR

[<column that contains the values that will become column headers>]

IN ( [first pivoted column], [second pivoted column],

... [last pivoted column])

) AS <alias for the pivot table>

<optional ORDER BY clause>;

Example:

IF object_id('Achievement') IS NOT NULL DROP TABLE Achievement
GO
CREATE TABLE Achievement
(
    Name VARCHAR(20),
    [Subject] VARCHAR(20),
    Scoring INT
)
GO
INSERT INTO Achievement
VALUES      ('Eric','Chinese',74),
            ('Eric','Mathematics',73),
            ('Eric','Physics',90),
            ('Eric','English',90),
            ('Brian','Chinese',64),
            ('Brian','Mathematics',84),
            ('Brian','Physics',84),
            ('Brian','English',89),
            ('Tim','Chinese',77),
            ('Tim','Mathematics',84),
            ('Tim','Physics',64),
            ('Tim','English',89)
GO
SELECT * FROM Achievement
Name                 Subject              Scoring
-------------------- -------------------- -----------
Eric                 Chinese              74
Eric                 Mathematics          73
Eric                 Physics              90
Eric                 English              90
Brian                Chinese              64
Brian                Mathematics          84
Brian                Physics              84
Brian                English              89
Tim                  Chinese              77
Tim                  Mathematics          84
Tim                  Physics              64
Tim                  English              89

--Turning the unique values of the column [Subjects] of the table [Achievement] into multiple columns output. (Static)

SELECT Name,Chinese,Mathematics,Physics,English

   2:  FROM 

   3:  (SELECT Name,[Subject], Scoring 

   4:      FROM Achievement) AS SourceTable

   5:  PIVOT

   6:  (

   7:  AVG(Scoring)

   8:  FOR [Subject] IN ([Chinese], [Mathematics], [Physics],[English])

   9:  ) AS PivotTable

Name                 Chinese     Mathematics Physics     English
-------------------- ----------- ----------- ----------- -----------
Brian                64          84          84          89
Eric                 74          73          90          90
Tim                  77          84          64          89

 

--Turning the unique values of the column [Subjects] of the table [Achievement] into multiple columns output and add the Total & Average columns. (Static)

 

   1:  GO

   2:  SELECT m.*,n.Total,n.Average

   3:  FROM

   4:  (

   5:      SELECT Name,Chinese,Mathematics,Physics,English

   6:      FROM 

   7:      (SELECT Name,[Subject], Scoring 

   8:          FROM Achievement) AS SourceTable

   9:      PIVOT

  10:      (

  11:      AVG(Scoring)

  12:      FOR [Subject] IN ([Chinese], [Mathematics], [Physics],[English])

  13:      ) AS PIVOTTable

  14:  ) AS m,

  15:  (

  16:      SELECT NAME ,SUM(Scoring) AS Total,CAST(AVG(Scoring*1.0) AS DECIMAL(18,2)) AS Average

  17:      FROM Achievement

  18:      GROUP BY Name

  19:  ) AS n

  20:  WHERE m.Name= n.Name

Name                 Chinese     English     Mathematics Physics     Total       Average
-------------------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
Brian                64          89          84          84          321         80.25
Eric                 74          90          73          90          327         81.75
Tim                  77          89          84          64          314         78.50

 

--By using SFUFF (Dynamic)

   1:  GO

   2:  DECLARE @sql VARCHAR(8000)

   3:  SET @sql='' 

   4:  SELECT @sql = @sql + ',' + [Subject] FROM Achievement GROUP BY [Subject] 

   5:  --same 

as

: SELECT @sql = @sql + ','+ [Subject] FROM (SELECT DISTINCT [Subject] FROM  Achievement) AS A

   6:  SET @sql = STUFF(@sql,1,1,'')

   7:  SET @sql = 'SELECT m.* , n.Total,n.Average FROM ' 

   8:           + '(SELECT * FROM Achievement a PIVOT (MAX(Scoring) for [Subject] in ('+@sql+')) b) AS m , '

   9:           + '(SELECT Name,SUM(Scoring) AS Total, CAST(AVG(Scoring * 1.0) AS DECIMAL(18,2)) AS Average FROM Achievement GROUP BY Name)AS n '

  10:           + 'WHERE m.Name= n.Name '

  11:  EXEC(@sql)

Name                 Chinese     English     Mathematics Physics     Total       Average
-------------------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
Brian                64          89          84          84          321         80.25
Eric                 74          90          73          90          327         81.75
Tim                  77          89          84          64          314         78.50

--By using ISNULL (Dynamic)

   1:  GO

   2:  DECLARE @sql VARCHAR(8000)

   3:  SELECT @sql=ISNULL(@sql+',','')+[Subject] FROM Achievement GROUP BY [Subject]

   4:  SET @sql = 'SELECT m.* , n.Total,n.Average FROM '

   5:          + '(SELECT * FROM (SELECT * FROM Achievement) a PIVOT (MAX(Scoring) for [Subject] in ('+ @sql+')) b) AS m ,'

   6:          + '(SELECT Name,SUM(Scoring) AS Total, CAST(AVG(Scoring*1.0) AS DECIMAL(18,2))AS Average FROM Achievement GROUP BY Name) AS n '

   7:          + 'WHERE m.Name= n.Name'

   8:  EXEC(@sql)

Name                 Chinese     English     Mathematics Physics     Total       Average
-------------------- ----------- ----------- ----------- ----------- ----------- ---------------------------------------
Brian                64          89          84          84          321         80.25
Eric                 74          90          73          90          327         81.75
Tim                  77          89          84          64          314         78.50

 

UNPIVOT : Rotating columns of a table-values expression into columns values

Example:

--Create table [Achievement]

   1:  IF object_id('Achievement') IS NOT NULL DROP TABLE Achievement

   2:  GO

   3:  CREATE TABLE Achievement

   4:  (

   5:      Name VARCHAR(20),

   6:      Chinese INT,

   7:      Mathematics INT,

   8:      Physics INT,

   9:      English INT

  10:  )

  11:  GO

  12:  INSERT INTO Achievement

  13:  VALUES    ('Brian',64,89,84,84),

  14:          ('Eric', 74,90,73,90),

  15:          ('Tim',  77,89,84,64)

  16:  GO

  17:  SELECT * FROM Achievement

Name                 Chinese     Mathematics Physics     English
-------------------- ----------- ----------- ----------- -----------
Brian                64          89          84          84
Eric                 74          90          73          90
Tim                  77          89          84          64

 

--Static way to use UNPIVOT

GO
SELECT Name,[Subject],Scoring
FROM Achievement
UNPIVOT (Scoring FOR [Subject] IN ([Chinese],[Mathematics],[Physics],[English])) t
Name                 Subject     Scoring
-------------------- ----------- -----------
Brian                Chinese     64
Brian                Mathematics 89
Brian                Physics     84
Brian                English     84
Eric                 Chinese     74
Eric                 Mathematics 90
Eric                 Physics     73
Eric                 English     90
Tim                  Chinese     77
Tim                  Mathematics 89
Tim                  Physics     84
Tim                  English     64

--Dynamic way to use UNPIVOT

 

   1:  GO

   2:  DECLARE @sql VARCHAR(8000)

   3:  SELECT @sql=ISNULL(@sql+',','')+QUOTENAME(Name)

   4:  FROM syscolumns

   5:  WHERE ID=object_id('Achievement') AND Name NOT IN ('Name')

   6:  ORDER BY Colid

   7:  SELECT @sql = 'SELECT Name,[Subject],[Scoring] FROM Achievement UNPIVOT([Scoring] for [Subject] in('+@sql+'))b'

   8:  EXEC(@sql)

Name                 Subject     Scoring
-------------------- ----------- -----------
Brian                Chinese     64
Brian                Mathematics 89
Brian                Physics     84
Brian                English     84
Eric                 Chinese     74
Eric                 Mathematics 90
Eric                 Physics     73
Eric                 English     90
Tim                  Chinese     77
Tim                  Mathematics 89
Tim                  Physics     84
Tim                  English     64