Dismiss
InnovationQ will be updated on Sunday, Oct. 22, from 10am ET - noon. You may experience brief service interruptions during that time.
Browse Prior Art Database

SQL Generator to Format Type Tables Into Columns

IP.com Disclosure Number: IPCOM000242226D
Publication Date: 2015-Jun-26
Document File: 2 page(s) / 132K

Publishing Venue

The IP.com Prior Art Database

This text was extracted from a PDF file.
This is the abbreviated version, containing approximately 62% of the total text.

Page 01 of 2

SQL Generator to Format Type Tables Into Columns

Author: Gary Koets Introduction

An algorithm is disclosed that generates SQL (Structured Query Language) to format row values into columns. This algorithm uses an initial SQL query to enumerate distinct attribute names, and then loops to generate a single SQL statement with a scalar correlated subquery for each attribute name.

Background

In a database, type tables are typically normalized such that each row represents a single attribute for a particular type. For example:

Job Type Name Attribute Name

Attribute Value

"JobName1"

"AttributeName1"

"JobName1"

"AttributeName2"

"JobName1"

"AttributeName3"

"JobName1"

"AttributeName4"

"JobName2"

"AttributeName1"

"JobName2"

"AttributeName2"

"JobName2"

"AttributeName3"

"JobName2"

"AttributeName4"

"JobName3"

"AttributeName1"

"JobName3"

"AttributeName2"

"JobName3"

"AttributeName3"

"JobName3"

"AttributeName4"

Table 1

Users however, often like to see the data in columnar format like this:

Job Type Name AttributeName1 AttributeName2 AttributeName3 AttributeName4

"JobName1"

"JobName2"

"JobName3"

Table 2

The algorithm described in this article pivots the attribute values within a particular type to reformat the data as shown in Table 2. This algorithm is useful for a database system that does not have built in pivoting functions. This algorithm performs well and does not require modifications when attributes are added or removed from Job Types.

January 12, 2011

1

Confidential


Page...