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: 7050

8/27/2013 10:47:38 AM

SQL - Script to display the contents of one line in a table VERTICALLY

A client has asked me to write an SSRS report that will show the value of all the fields in a table. This particular table has 140 fields, that means that I would have to place a grid on the form that would have 140 columns (imagine if you tried to print that) or I'd have to place 280 text boxes on the form and format the whole think pretty. That would take hours.

I've written a script that fixes that issue.

A picture is better than a thousand words, right?

We have a single line in a table, it looks like this (same as any other table). You'll have to imagine the table extending to the right for 140 fields.

This script will 'pivot' the table and make it look like this:

I have the output coming in two sets of columns (columnName, Value, ColumnName, Value, RowID) instead of just one; but the script can be easily altered to do this for any number of columnName, Value pairs.
Version: Unknown or N/A
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