Monday, 26 August 2013

SQL- PIVOT and UNPIVOT

You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table. PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.


The following is annotated 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>;

The following code example produces a three-column table that has seven rows.

SELECT TOP 1000 [MarksID]
      ,[CustID]
      ,[Marks]
  FROM [DBSK].[dbo].[Marks]

Here is the result set.
MarksID               CustID         Marks

1                              1              234      
2                              1              3223     
3                              2              32       
4                              2              43       
5                              3              434      
6                              4              4343     
7                              4              321           


The following code displays the same result, pivoted so that the CustID values become the column headings.Now I have to get Max Msrks for Every CustID.

  select [1],[2],[3],[4] from (select CustID,Marks from Marks) as temptable
  pivot(max(Marks) for CustID in ([1],[2],[3],[4])) as MaxMarks

Here is the result set.
1                      2              3              4
3223               43           434         4343  

for the same result you can use the Different Query using temptable
  select CustID,Marks
  into #temptable
  from Marks
  select [1],[2],[3],[4] from #temptable
  pivot(max(Marks) for CustID in ([1],[2],[3],[4])) as MaxMarks
  drop table #temptable


UNPIVOT performs almost the reverse operation of PIVOT, by rotating columns into rows. 

--Create the table and insert values as portrayed in the previous example.
CREATE TABLE pvt (VendorID int, Emp1 int, Emp2 int,
    Emp3 int, Emp4 int, Emp5 int);
GO
INSERT INTO pvt VALUES (1,4,3,5,4,4);
INSERT INTO pvt VALUES (2,4,1,5,5,5);
INSERT INTO pvt VALUES (3,4,3,5,4,4);
INSERT INTO pvt VALUES (4,4,2,5,5,4);
INSERT INTO pvt VALUES (5,5,1,5,5,5);
GO

Here is the result set of Table.
VendorID            Emp1     Emp2     Emp3     Emp4     Emp5
1                              4              3              5              4              4
2                              4              1              5              5              5
3                              4              3              5              4              4
4                              4              2              5              5              4
5                              5              1              5              5              5 


Now suppose, you want to rotate the column identifiers Emp1Emp2Emp3Emp4, andEmp5 into row values that correspond to a particular vendor. This means that you must identify two additional columns. The column that will contain the column values that you are rotating (Emp1Emp2,...) will be called Employee, and the column that will hold the values that currently reside under the columns being rotated will be called Orders. These columns correspond to the pivot_column and value_column, respectively, in the Transact-SQL definition. Here is the query.

--Unpivot the table.
SELECT VendorID, Employee, Orders
FROM
   (SELECT VendorID, Emp1, Emp2, Emp3, Emp4, Emp5
   FROM pvt) p
UNPIVOT
   (Orders FOR Employee IN
      (Emp1, Emp2, Emp3, Emp4, Emp5)
)AS unpvt;
GO

Here is the result set.
VendorID            Employee            Orders
1                              Emp1                     4
1                              Emp2                     3
1                              Emp3                     5
1                              Emp4                     4
1                              Emp5                     4
2                              Emp1                     4
2                              Emp2                     1
2                              Emp3                     5
2                              Emp4                     5
2                              Emp5                     5
3                              Emp1                     4
……….
.......







7 comments:

  1. hi, Please help me um just lost. Um trying to pivot my table in VB6 and make report or present in datagrid. My table "Grades" is like this (MS Access):

    ID SubCode Test1
    DM10001-13 MAT4 23
    DM10001-13 SCI4 45
    DM10001-13 ENG4 67
    DM10001-13 SIS4 89
    DM10001-13 AGR4 90
    DM10001-13 FF4 98
    DM10001-13 FN4 76
    DM10001-13 BIO4 56
    CT10002-13 MAT4 43
    CT10002-13 SCI4 34
    CT10002-13 ENG4 56
    CT10002-13 SIS4 77
    CT10002-13 AGR4 50
    CT10002-13 LIT4 89
    JM10003-13 MAT4 100
    JM10003-13 SCI4 12
    JM10003-13 ENG4 35
    JM10003-13 SIS4 67
    JM10003-13 AGR4 87
    JM10003-13 ACC4 98
    JM10003-13 ECO4 76
    MT10004-13 MAT4 100
    MT10004-13 SCI4 45
    MT10004-13 ENG4 34
    MT10004-13 SIS4 23
    MT10004-13 AGR4 67
    MT10004-13 ACC4 56
    MT10004-13 ECO4 77

    And I want it to look like this:
    ID MATHS SCIENCE ENGLISH SISWATI AGRIC
    DM10001-13 23 45 67 89 90
    CT10002-13 43 34 56 77 50
    JM10003-13 100 12 35 67 87

    *not included all subjects. Please help

    ReplyDelete
  2. ooh! its
    ID..................SubCode........Test1
    DM10001-13......MAT4.............23

    And...
    ID......................MATHS.......SCIENCE......ENGLISH......SISWATI.........AGRIC
    DM10001-13........23................45...................67................89.................90

    ReplyDelete
  3. SELECT [ID], [MAT] 'MATHS',[SCI] 'SCIENCE',[ENG] 'ENGLISH',[SIS] 'SISWATI',[AGR]'AGRIC'
    FROM
    (
    SELECT [SubCode], [ID], [Marks]
    FROM StudentMarks --or [Grade]
    ) AS tempGrade
    PIVOT
    (
    MAX([Marks]) --max / min doesnt matter as data will be unique
    FOR [SubCode] IN ([MAT],[SCI],[ENG],[SIS],[AGR])
    ) as pvt

    ReplyDelete
    Replies
    1. Thanx very much Andrew. I tried the code but everything was hieghlighted in red. I used the code below. on line (2), it showed an error on [MAT4]'Maths' and the other sets. Also {separator or )} on line (11) "FOR". Lastly, should i break it as shown? the use of line (4) and line (9) is new to me on VB6.

      i THANK YOU
      1.Private Sub cmdCustomized_Click()
      2.rs.Open "SELECT [ID], [MAT4] 'MATHS', [SCI4] 'SCIENCE',[ENG4] 'ENGLISH',
      [SIS4] 'SISWATI', [AGR4] 'AGRIC'"
      3.From
      4.(
      5.SELECT [SubCode], [ID], [TITest1]
      6.From Grades
      7.) AS tempGrades
      8.Pivot
      9.(
      10.Max ([T1Test1])
      11.FOR [SubCode] IN ([MAT4],[SCI4],[ENG4],[SIS4],[AGR4])
      12.) as pvt ",con, 1, 3
      13.Set DataReport3.DataSource = rs
      14.DataReport3.Show
      15.End Sub

      Delete
    2. Hi,
      You can Create a View for Pivot in SQL Server. then Call this View from VB Code. I think this will be helpfull for You.

      Delete
    3. Hi Dumisa Mabuza,
      Try executing the code by calling stored procedure instead of writing inline queries.
      try something like shown below..
      Set cmd = New ADODB.Command
      cmd.ActiveConnection = con
      cmd.CommandType = adCmdStoredProc
      cmd.CommandText = "Stored_Procedure_Name"

      Set rs = cmd.Execute
      Set DataReport3.DataSource = rs
      ---
      you should make a stored procedure in database and thn call it from vb6 code. I am not from vb6 background and also you would require a sql server 2008 for this.

      Delete
  4. wow thanx very much guys, i will research about using sql server. For now I created a temporal table to hold the data i need, then rank and create report. Keep up with the good work!

    ReplyDelete

Topics

ADO .Net (2) Ajax (1) Angular Js (17) Angular2 (24) ASP .Net (14) Azure (1) Breeze.js (1) C# (49) CloudComputing (1) CMS (1) CSS (2) Design_Pattern (3) DI (3) Dotnet (21) Entity Framework (3) ExpressJS (4) Html (3) IIS (1) Javascript (6) Jquery (9) Lamda (3) Linq (11) Mongodb (1) MVC (48) NodeJS (7) RDLC (1) Report (1) Sql Server (29) SSIS (3) SSRS (2) UI (1) WCF (12) Web Api (10) Web Service (1) XMl (1)