SAP Knowledge Base Article - Public

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

Symptom

You have a data set in the form of

  • Column names = A,B,C,D
  • Row Values are P or Q
A B C D
P Q Q P
Q P Q Q
P P Q 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
Column PTotal QTotal

A

2 1
B 2 1
C 0 3
D 1 2

Resolution

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

Keywords

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

Product

SAP Crystal Reports XI