CodePartners - Software Development - Dallas, Texas Software Development - Sage Accpac Customization - SageCRM Customization
Login
pay invoice online  |  remote assistance  |  
  • INTERNET APPLICATION DEVELOPMENT

    • Services
      • Web Application Development
      • Mobile Applications
      • Integration Solutions
      • Staff Augmentation
    • Technologies
    • How we work
    • About us
    • Success stories
    • Blog
  • MID MARKET ERP DEVELOPMENT

    • Intacct
    • Sage CRM
    • Sage
    • Sage Component Catalog
    • How We Work
    • About Us
    • Success Stories
    • Blog

Blog

CodePartners Blog

rss


Use Pivot Operator to Pivot Data

SHARE THIS
Facebook twitter linkedin Print Print
Print Email

By Emma Tsai

Performing functional testing for software applications is part of the job as a business analyst. Occasionally, I need to work with SQL server to do database queries so that I can verify that the application pulls data results as expected. Recently, I learned how to use PIVOT operator to group and aggregate data in SQL database. Here is how it works:

In pivot queries, here are the three elements you need to identify first:

1.       What do you want to see on rows? Also known as grouping elements

2.       What do you want to see on columns? Also known as spreading elements

3.       What do you want to see in the intersection of each distinct row and column? Also known as aggregation elements

The following example assumes that you want to query the Sales.Orders table. You want to return a row for each distinct customer (grouping element), a column for each distinct shipper (spreading element), in this case [1], [2], [3], and the sum of freight value (aggregation element).

Selecting data for PIVOT query

I already defined a table expression (like the one named PivotData) that returns the three elements needed in pivoting instead of querying the underlying source table directly. In the specification of a PIVOT operator, the grouping element is identified by elimination, which is what is left from the queried table besides the spreading and aggregation elements. If you query the underlying table directly (Sales.Orders in this case), all columns from the table, except for the freight and shipperid columns, will implicitly be your grouping elements. Instead of getting a row per customer, you got a row per order, which returns 830 rows back (since there are 830 rows in the Sales.Orders table).

Pivot quesry results

I hope this tip can help you pivot data more efficiently.

 

Reference:  Exam 70-461 Querying Microsoft SQL Server 2012 by Itzik Ben-Gan, Dejan Sarka, and Ron Talmage




Comments are closed.
On August 27, 2014 in Development, Project Management, Web Development [Post:by]
Tagged With: query, SQL, Pivot / 2492 Views

Search

Categories

  • General (166) rss
  • Development (158) rss
  • Success Stories (40) rss
  • Products (29) rss
  • Uncategorized (18) rss
  • BTerrell Group (5) rss
  • Project Management (22) rss
  • Web Development (57) rss
  • Web application (33) rss
  • Integration (19) rss
  • Mobile Development (7) rss

Archives

  • 2015
    • January (5)
    • February (7)
    • March (6)
    • April (5)
    • May (2)
  • 2014
    • January (13)
    • February (12)
    • March (13)
    • April (12)
    • May (11)
    • June (12)
    • July (11)
    • August (13)
    • September (10)
    • October (8)
    • November (6)
    • December (4)
  • 2013
    • April (73)
    • May (22)
    • June (20)
    • July (20)
    • August (22)
    • September (19)
    • October (13)
    • November (12)
    • December (7)

Tags

.NET Accpac app development application customization application development automation Azure C# cloud components Crystal Reports customization development ERP general ledger HTML5 Intacct Intacct Platform Services integration JQuery load balancer mass update mobile development OCR Order Entry outsourcing PO pricing product development project management REST Sage 300 ERP Sage CRM software integration trigger user interface Visual Studio web application web development website development Workforce Go! .NET ACA accpac Ajax Amazon AP invoices application development Arxis ASP.NET automation Axure BTerrell Group C# Cloud computing CodePartners Constant Contact credit card processing CRM crystal reports custom software Customer Service Customization Delete team project design phase Development doc-link DRILLDWNLK ERP event Excel GLAFS Intacct Intacct Advantage Intacct Platform Services Integration Java script JavaScript jQuery Kerr Consulting LinkedIn Microsoft MSSQL Offshoring outsourcing payroll PCI compliance PCI-DSS Pivot PJC project management sage Sage 300 Sage 300 ERP Sage Accpac ERP Sage CRM Smart Rule social media software testing SQL SQL Query SQL Server staff augmentation sub-ledger success success story TFS Trouble shooting UI design Visual Studio WCF Web API web application web design web development Webinar website Windows Windows Azure XML
Email this article from Codepartners.com
Separate multiple addresses with commas(,)
To: *   Copy me
Subject:
From: *
Message:
ABOUT US / CONTACT US / NEWS & EVENTS
CodePartners / 16200 Addison Rd. STE 270 / Addison, Texas 75001 / Phone: 214-647-2611 / Toll Free: 866-647-2611 / Fax: 214-647-2612 / info@codepartners.com
© 2006 - 2015 codepartners.com All Rights Reserved.