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


How to Un-Pivot Data

SHARE THIS
Facebook twitter linkedin Print Print
Print Email

by Emma Tsai

In my last blog, I talked about how to use PIVOT operator to group and aggregate data in SQL database. Today, I am going to share how to Un-pivot data.

The starting point is some pivoted data. I used a sample table called Sales.FreightTotals as an example. The sample data shows as below:

Pivot table

As we can see, the source table has a row for each customer and a column for each shipper. The intersection of a customer and shipper is the total freight values. The Un-pivoting task is to return a row for each customer, the shipper ID in a second column, and the freight value in a third column.

In every un-pivoting task, we need to identify the three elements involved:

  • Source columns that you are un-pivoting, in this case, [1], [2], and [3]
  • The name you want to assign to the target values column in this case, freight
  • The name you want to assign to the target names columns, in this case, shipperid

After identifying the three elements, you can use this query to un-pivot data.

 unpivot query

In addition to un-pivoting the data, the UNPIVOT operator filters out rows with NULLs in the value column (freight).   

In summary, PIVOT rotates data from a state of rows to a state of columns; While UNPIVOT rotates the data from columns to rows.

 

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

 




Comments are closed.
On October 1, 2014 in General [Post:by]
Tagged With: data management, Pivot / 1711 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.