Free Online Courses for Software Developers - MrBool
× Please, log in to give us a feedback. Click here to login
×

You must be logged to download. Click here to login

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

×

MrBool is totally free and you can help us to help the Developers Community around the world

Yes, I'd like to help the MrBool and the Developers Community before download

No, I'd like to download without make the donation

SQL server Pivot Table Construction

A pivot table is a series instrument that allows us to restructure and abridge chosen columns and rows of information in a spreadsheet or database tuple to get a chosen statement. In this article we will discuss about the pivot table.

A pivot table is a regularly used technique of shortening and displaying particular report information by means ofconsortium andaggregating values. Pivot tables are easily shaped by office users using Microsoft Excel or MS Access. Sincepivot table enables information builders and BI (Business Intelligence) specialists authorize their appearance of information and amplify the visibility andunderstandabilityof mined information, pivot tables are widespread and favored widely. Pivot tables exhibit information in tabular form. The pivot table formatting is not dissimilar than a tabular report formatting. But the tuples are produced by the statement information itself.

Microsoft SQL Server has incorporated the PIVOT and UNPIVOT guidelines as enhancements to t-sql with MS SQL Server 2005.

In MS SQL Server 2008, we can employ the PIVOT command and UNPIVOT command to construct and use pivot tables in sql. T-SQL Pivot and Unpivot declaration will alter and turn a tabular data into one more tuple worth information in sql. Because Pivot / Unpivot are SQL2005 t-sql improvement. Databases which we would like to implement pivot and unpivot commands be supposed to be at slightest compatibility level 80 (SQL2005) or 90 (SQL2008).

Usage

Microsoft SQL Server has incorporated the PIVOT and UNPIVOT commands as enhancements to t-sql with SQL Server 2005. In 2008, we can immobile the PIVOT command and UNPIVOT command to make and use in Sql. T-SQL Pivot and Unpivot declaration will alter and rotate approximately a tabular information into one more tuple value information in Sql. Since Pivot / Unpivot are SQL2005 t-Sql improvement, databases which we would like to implement pivot and unpivot commands should be at slightest compatibility level 80 (SQL2005) or 90 (SQL2008).

T-SQL Pivot Syntax

T-SQL PIVOT sentence structure is not clearly acknowledged in the MSDN or on SQL Server BOL (Books Online) but universal utilization of Pivot command can be abridged as follows.

Listing 1. Sample Pivot command

 SELECT  [non-pivoted column], -- optional  
    [additional non-pivoted columns], -- optional
    [first pivoted column],  [additional pivoted columns]
  FROM (
    SELECT query generating sql information for pivot  
   AS Tuple Alias PIVOT (
    (Column for aggregation or measure column) 
     -- MIN,MAX,SUM,etc  FOR []  IN (
      1st column, to, last column)) AS alias ORDER BY clause 
  

Obtainable in SQL Server 2005/2008, the Pivot relational hand is worn to translate rows into columns information. This characteristic is commonly worn in hearsay and is appealing trouble-free to employment with.

At this time is an instance

We have a tuple Prod, if we implement this undemanding query:

  SELECT prodId,yr,amnt
  FROM Prod
  

We have this result:

prodId yr amnt

124 2010 125
125 2010 454
126 2010 75
127 2012 256
128 2014 457
129 2014 585
130 2012 142
131 2012 785

At present we are obtainable to employ PIVOT to contain the existence of columns and for a prodId, the sum of amounts for 2012 and 2014.

Listing 2.Sample showing Pivot implementation

 SELECT * FROM
  (
      SELECT prodId,yr,amnt
      FROM Prod
  )t
  PIVOT (SUM (amnt) FOR yr
  IN ([2012], [2014])) AS pvt
 

So, we will have this result:

prodId 2012
2014

124
125 NULL

125
454 NULL

126
75 NULL

127 56 67

128
36 56

129
24 57

Okay, that’s good however if we judge so as to we are not familiar with the names of the columns, we have to create our PIVOT forcefully. Look at the subsequent code:

We are primarily departing to make a string that concatenates all years

Listing 3. Sample showing concatenation

 DECLARE @yr VARCHAR(500)
  SELECT  @yr = STUFF(( SELECT DISTINCT
                          '], [' + ltrim(str(yr))
                          FROM    Prod
                          ORDER BY '], [' + ltrim(str(YEAR(yr)))
                          ), 0, 1, '') + ']'
  

So this thread will surround all the years desirable for our PIVOT question:

[2010],[2012],[2013],[2014],[2015]

Subsequent to that, all we can also generate our PIVOT query. Here is the absolute query:

Listing 4. Sample showing Pivot query

 DECLARE @query VARCHAR(400)
  DECLARE @yrs VARCHAR(200)
  SELECT  @yr = STUFF(( SELECT DISTINCT
                          '],[' + ltrim(str(yr))
                          FROM    Prod
                          ORDER BY '],[' + ltrim(str(YEAR(yr)))
                          ), 0, 1, '') + ']'
   
  SET @query =
  'SELECT * FROM
  (
      SELECT prodId,yr,amnt
      FROM Prod
  )t
  PIVOT (SUM(amnt) FOR yr
  IN ('+@years+')) AS pvt'
   
  EXECUTE (@query)
 

And at this point the result is displayed in consequence:

prodId 2010 2012 2013 2014 2015

124 125 210 NULL NULL NULL
125 459 295 NULL NULL NULL
126 751 290 NULL NULL NULL
127 NULL 256 NULL NULL NULL

So, to allow this characteristic, we have to put the compatibility intensity of our database to a advanced level by the subsequent stored procedure:

 --If you are running SQL 2005 
  EXEC sp_dbcmptlevel 'myDatabaseName’
  --If you are running SQL 2008 
  EXEC sp_dbcmptlevel 'myDatabaseName' 
  

Pivot tables are influential apparatus in SQL (Structured Query Language) that permit us to generate a tuple or cross-tab like to an Excel spreadsheet from the stuffing of a database. It doesn’t mean a topic what the database is--SQL Server, MS Access, Sybase, etc.--as extended as it supports SQL.

We require being familiar with the name of the tuple we desire to produce the pivot table with and the names of the fields we wish to be in the pivot table. We will also require knowing how to generate a query in our exacting database.

Supplementary communities are comprehension

Instructions

  1. Open the tuple we covet to produce the pivot table in and seem at the information and fields. Conclude which fields we wish for in the pivot table and what computation we wish to perform. Possibly it is a tuple that tracks purchase commissions at various agencies that symbolize our effort and we wish to analysze the monthly purchase by every agency. For this instance, produce a line for every agency and a column for every month. Employ the SQL arrangement purpose to format the information so it reads obviously in the tuple.
  2. Unwrap the SQL query window in our database software and go into the basic SQL statement to generate pivot tables. We can employ the usual syntax of " tuplename.fieldname, " but though this will be on a single tuple, we can presently employ the field names. For lucidity, keywords will be in uppercase and tuple and field names will be in [square brackets]. We can use this technique for inscription the real query.
  3. Form the fundamental query to get in progress:
    TRANSFORM function ([field for calculation]) as variablename1
               SELECT [field for row], function ([field for calculation]) as 
                variablename2
               from [tablename]
               GROUP by [field for row]
               PIVOT [field for columns]
    

    "Function" is the computation we want to carry out for theremaining of the tuple. In this case, it will be "sum," as we wanta sum of commissions. We could utilize "average" or severalextra function that gives us the consequences we require.
  4. Penetrate the query as exposed on top of, substituting our field and tuple names, and see the consequences. It may not be what we are looking for other than it will give us scheme of how it works, and then we can fine-tune it by means of usual SQL functions and grammar. In this case, we wish to arrange the commissions in currency plan, and we wish to format the purchasedate field to exhibit months in the three-letter contraction. Once more, using the instance cited over, the total query would look like this:
          
    TRANSFORM format (Sum ([Purchase 2010].Commission), " currency") AS SumOfCommission
               SELECT [Purchase 2010].Agency, format 
              (Sum ([Purchase2010].Commission), 
                " currency" ;) AS [Total Of Commission]
               FROM [Purchase 2010]
               GROUP BY [Purchase 2010].Agency
               PIVOT Format ([PurchaseDate], "mmm") In            
               ("Jan","Feb","Mar"
               ,"Apr"           
                ,"May"
    

So I just came with a modest data structure. A number of users required to start storing in series on the area under discussion with more than a few of their credentials. The problem was that the attributes of one article possibly will be exclusively remarkable than one more. By that I connote a secretarial document which might have version and volume while a technical document power have amendment and liberate. We could create the tuple actually broad and merely inhabit the fields that connected to that article, but that seemed like an actually awful design. So I determined to employ a recursive tuple to grip it.

With a recursive tuple I could inhabit (n) numeral of attributes for an essay and connect them to the article via its id DocumentID. I have all the attributes and if supplementary get extra I can immediately add on them to my recursive tuple. Other than, the difficulty is what if I fancy presenting or going again those in a trampled structure (tuple)? So what I did to resolve that is use an amalgamation of self-motivated sql, SQL Server 2008 amalgamate, and a worldwide Temp Tuple.

Here is how I did it:

Listing 5. Sample showing temp table

 IF EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID(N'[dbo].[DAttributes]') AND type in (N'U'))
      DROP TABLE [dbo].[DocmntAttrbuts]
  GO
  IF EXISTS (SELECT * FROM sys.objects 
  WHERE object_id = OBJECT_ID (N'[dbo]. [Docmnts]') AND type in (N'U'))
  DROP TABLE [dbo].[Docmnts]
   
  GO
   
  CREATE TABLE [dbo].[Docmnts](
      [DID] [int] IDENTITY(1,1) NOT NULL,
      [DName] VARCHAR(50) NOT NULL,
      [DDescrip] VARCHAR(100) NOT NULL);
       
  GO
   
  ALTER TABLE [dbo].[Docmnts] ADD CONSTRAINT PK_Docmnts 
   PRIMARY KEY CLUSTERED 
  ([DID] ASC) ON [PRIMARY];
   
  GO
   
  Insert Into dbo.Docmnts(DName,DDescrip)
  Values('Account','Accounts 2007');
   
  Insert Into dbo.Docmnts(DocumentName,DocumentDescrip)
  Values('Account','Accounts 2008');
   
  GO
   
  CREATE TABLE [dbo].[DAttributes](
      [DAttributeID] [int] IDENTITY(1,1) NOT NULL,
      [DID] [int] NOT NULL,
      [Attribute] [varchar](50) NOT NULL,
      [AttributeVal] [varchar](40) NOT NULL,
      [ParentDocumentID] [int] NULL)
   
  GO
   
  ALTER TABLE [dbo].[DAttributes] ADD CONSTRAINT PK_DAttributes   
    PRIMARY KEY CLUSTERED 
 ([DAttributeID] ASC) ON [PRIMARY];
   
  GO
   
  ALTER TABLE [dbo].[DAttributes] ADD CONSTRAINT FK_DAttributes 
  FOREIGN KEY([DocmntID]) 
 REFERENCES [dbo].[Docmnts]([DID]);
   
  GO
   
  ALTER TABLE [dbo].[DAttributes] CHECK CONSTRAINT 
   [FK_Docmnts_DAttributes]
   
  GO
   
   
  Insert Into dbo.DAttributes
  Values(1,'Revision','1',NULL);
   
   
  Insert Into dbo.DAttributes
  Values(1,'Version','1',NULL);
   
  Insert Into dbo.DAttributes
  Values(2,'Revision','2',NULL);
   
   
  Insert Into dbo.DAttributes
  Values(2,'Version','2',NULL);
   
  Insert Into dbo.DAttributes
  Values(1,'Volume','2',NULL);
   
  Insert Into dbo.DAttributes
  Values(1,'Release','5',NULL); 
   
  GO
   
  DECLARE @DName VARCHAR(25) = 'Account'
  DECLARE @ColumHeaderVARCHAR(max) = '';
   
  Select
      @ColumHeader=   @ColumHeader+ 
       '[' + xx.Attribute + ']' + ' varchar(100),'
  From (Select distinct attribute 
        From dbo.DAttributes da 
        inner join dbo.Docmnts d on d.DID = da.DID
        Where d.DName = @DName) xx
   
   
  Select @ColumHeader= substring(@ColumHeader,1,
   LEN(@ColumHeader) -1)
   
   
  DECLARE @SQLTable NVARCHAR(Max) = 'CREATE TABLE ##Pivot(
                                     DID       INT ,
                                     ParentDocumentID INT,'
                                     + @ColumHeader+ ' )';
   
  IF OBJECT_ID('tempdb..##Pivot') IS NOT NULL
      DROP TABLE ##Pivot;                
  exec sp_executesql @SQLTable;
   
   
  DECLARE @DID INT = 1,
          @RowCnt INT = 0,
          @Cnt    INT = 0,
          @PvtColumn varchar(100),
          @Column    varchar(100)
           
  Select
      @RowCnt = COUNT(da.Attribute)
  From dbo.DAttributes da 
  inner join dbo.Docmnts d on d.DID = da.DID
  Where d.DName = @DName;
   
   
  WHILE(@Cnt <= @RowCnt)
  BEGIN
   
      Select 
              @Column = Attribute
      From (Select
               ROW_NUMBER() OVER(ORDER BY DAttributeID) RowID
              ,Attribute
            From dbo.DAttributes da) xx
      Where xx.RowID = @Cnt;
       
      SET @PvtColumn = '[' + @Column + ']';
   
      DECLARE @SQL NVARCHAR(max);
      DECLARE @MergeSQL NVARCHAR(max);
       
      SET @SQL = 'Select DID,PDocumentID,' + @PvtColumn + '
      From (Select DID,PDocumentID,' + @PvtColumn + '
                  from DocumentAttributes
                  PIVOT(
                      MAX(AttributeVal)
                      FOR Attribute IN (' + @PvtColumn + ')
   
                  ) pvt 
                          ) xx
      Where xx.' + @Column + ' IS NOT NULL';
       
      SET @MergeSQL = 'MERGE ##Pivot AS target
                   USING (' + @SQL + ') AS source
                  (DID,PDocumentID,' + @Column + ')
                      ON (target.DID = source.DID)
                     CASE WHEN MATCH THEN 
                          UPDATE SET target.' + @Column + 
                           ' = source.' + @Column + '
                    CASE WHEN NOT MATCH THEN    
                          INSERT (DID,PDocumentID,' + @Column + ')
                          VALUES (source.DID,source.PDocumentID,source.' 
                          + @Column + ');';
       
      execute sp_executesql @MergeSQL
       
      SET @Cnt += 1;
   
  END
   
   
  Select *
  from dbo.DAttributes
   
  Select *
  from ##pivot;
  DROP TABLE ##Pivot;
   
  GO
 

Step 2: Grip the attributes from the DAttributes tuple and employ those with dynamism and construct a universal Temp tuple with the attributes as column headers.

Listing 6. Sample showing universal Temp tuple

 DECLARE @ColumHeader VARCHAR(max) = '';
  Select
      @ColumHeader =   @ColumHeader+ '[' + xx.Attribute + ']' + 
      ' varchar(100),'
  From (Select distinct attribute 
        From dbo.DAttributes da 
        inner join dbo.Docmnts d on d.DID = da.DID
        Where d.DName = @DName) xx
   Select @ColumHeader= substring(@ColumHeader,1,LEN
    (@ColumHeader) -1)
   DECLARE @SQLTable NVARCHAR(Max) = 'CREATE TABLE ##Pivot(
                                     DID INT ,
                                     PDocumentID INT,'
                                     + @ColumHeader+ ' )';
   
  IF OBJECT_ID('tempdb..##Pivot') IS NOT NULL
      DROP TABLE ##Pivot;                
  exec sp_executesql @SQLTable;
   DECLARE @DID INT = 1,
          @RowCnt INT = 0,
          @Cnt    INT = 0,
          @PvtColumn varchar(100),
          @Column    varchar(100)
  Select
      @RowCnt = COUNT(da.Attribute)
  From dbo.DAttributes da 
  inner join dbo.Docmnts d on d.DID = da.DID
  Where d.DName = @DName;
 

Step 3: Circle from side to side the rows in the attributes tuple and use them to assemble two active sql statements. The first active declaration pivots the information so I can squash the tuple. The second active declaration takes the first and executes a combination on it (New with SQL Server 2008). The combine procedure allows us to do together insert /update in one pounce so we don’t have to verify if exists then do Insert or update depending on the result.

Listing 7. Sample showing active statement

 WHILE(@Cnt <= @RowCnt)
  BEGIN
   
      Select 
              @Column = Attribute
      From (Select
               ROW_NUMBER() OVER(ORDER BY DAttributeID) RowID
              ,Attribute
            From dbo.DAttributes da) xx
      Where xx.RowID = @Cnt;
       
      SET @PvtColumn = '[' + @Column + ']';
   
      DECLARE @SQL NVARCHAR(max);
      DECLARE @MergeSQL NVARCHAR(max);
       
      SET @SQL = 'Select DID,PDocumentID,' + @PvtColumn + '
      From (Select DID,PDocumentID,' + @PvtColumn + '
                  from DAttributes
                  PIVOT(
                      MAX(AttributeVal)
                      FOR Attribute IN (' + @PvtColumn + ')
   
                  ) pvt 
                          ) xx
      Where xx.' + @Column + ' IS NOT NULL';
       
      SET @MergeSQL = 'MERGE ##Pivot AS target
                    USING (' + @SQL + ') AS source 
                   (DID,PDocumentID,' + @Column + ')
                      ON (target.DID = source.DID)
                     Case WHEN MATCH THEN 
                          UPDATE SET target.' + @Column + 
                          ' = source.' + @Column + '
                    Case WHEN NOT MATCH THEN    
                          INSERT (DID,PDocumentID,' + @Column + ')
                          VALUES (source.DID,source.PDocumentID,source.' 
                          + @Column + ');';
       
      execute sp_executesql @MergeSQL
       
      SET @Cnt += 1;
   
  END
   
  Select *
  from dbo.DocumentAttributes
   
  Select *
  from ##pivot;
  DROP TABLE ##Pivot;
  

PowerPivot is Microsoft's new skill that leverages Microsoft SQL Server 2008 R2, Office 2010, and Sharepoint 2010 to make available an exhilarating new Self overhaul Business Intelligence display place.

PowerPivot Walkthrough

Using the Design ribbon within the PowerPivot transom, we can describe relations in the middle of the information tuples, if indispensable. Contained by an information resource, information relations are usually definite, but this user crossing point allows us to generate relations amid numerous information sources or if a meticulous requisite connection is not there.

Premeditated columns can be shaped by means of DAX (Data Analysis Expressions), an extraordinary new grammar for Power- Pivot that extends usual Excel procedures to give commanding active aggregation capabilities. The instance shows more than a straightforward periphery computation as the dissimilarity amid the Price and the Sale Amount. The resemblance with Excel terminology will permit present Excel users to employ their obtainable skills while as long as straightforwardness of the user crossing point.

PowerPivot can be worn with any information basis but in this case, the basis information comes from a relational information store supported by SQL Server 2008 R2. The authority at this time is so as to similar DAX appearance can be completed on any tuple with a connection with the FactSale tuple giving aggregation by all the rows in that measurement tuple.

Conclusion

We have discussed a lot about PivotTable. But the reader should remember that the implementation is situation dependent. If the requirement matches then only we should implement PivotTable. Hope the details will help you to implement PivotTable successfully in your application. Enjoy reading mrbool.



Website: www.techalpine.com Have 16 years of experience as a technical architect and software consultant in enterprise application and product development. Have interest in new technology and innovation area along with technical...

What did you think of this post?
Services
[Close]
To have full access to this post (or download the associated files) you must have MrBool Credits.

  See the prices for this post in Mr.Bool Credits System below:

Individually – in this case the price for this post is US$ 0,00 (Buy it now)
in this case you will buy only this video by paying the full price with no discount.

Package of 10 credits - in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download few videos. In this plan you will receive a discount of 50% in each video. Subscribe for this package!

Package of 50 credits – in this case the price for this post is US$ 0,00
This subscription is ideal if you want to download several videos. In this plan you will receive a discount of 83% in each video. Subscribe for this package!


> More info about MrBool Credits
[Close]
You must be logged to download.

Click here to login