1591011 - How to change a pivot table data set to relational format

You have a data set in the form of

• Column names = A,B,C,D
• Row Values are P or Q

You want to change the data set to appear with summaries of P and Q for each column, in the form of

• Column names = Column,PTotal,QTotal
• Row values are A-D, and Sum

On the original dataset, use a query that summarizes each column, and then performs a union of all, into one data set.

*Note that the syntax below will not work in all databases, so you would need to do a little research to find equivalent syntax for your version/type

SELECT  `A` as [Column], 

               PTotal = CASE ColumnA

When ColumnA = 'P' then 1
Else 0
END

               QTotal = CASE ColumnA

When ColumnA = 'Q' then 1
Else 0
END

FROM   Table

UNION ALL

SELECT  `B` as [Column], 

               PTotal = CASE ColumnB

When ColumnB = 'P' then 1
Else 0
END

               QTotal = CASE ColumnB

When ColumnB = 'Q' then 1
Else 0
END

FROM   Table

UNION ALL

SELECT  `C` as [Column], 

               PTotal = CASE ColumnC

When ColumnC = 'P' then 1
Else 0
END

               QTotal = CASE ColumnC

When ColumnC = 'Q' then 1
Else 0
END

FROM   Table

UNION ALL

SELECT  `D` as [Column], 

               PTotal = CASE ColumnD

When ColumnD = 'P' then 1
Else 0
END

               QTotal = CASE ColumnD

When ColumnD = 'Q' then 1
Else 0
END

FROM   Table

KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Problem