Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
Points: 6700

6/7/2017 2:39:03 PM

SQL Pivot code

* This article, and all our great SQL (General) documentation, Is available on the SQL (General) menu

I get asked all the time if I know how to PIVOT SQL data. I do, but I've never used the PIVOT keyword. The problem is that usually the query needs to be put in a report or a SmartList, and PIVOT will return a varying amount of columns. We need a fixed number of columns in reporting. 

The technique below will achieve that.

This is not 'fringe' code... you'll use this all the time. Take the time to read and understand. It's short. 

The sample will run as-is, cut and paste into SQL
Version: All
Section: SQL Scripts

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables