Transpose Columns into Rows?

[cursedkorok]

Hi All,

Hoping someone might be able to help, I'm building a report and think I need to transpose the columns into rows or do some sort of crosstab, please see below for an example of the data I'm getting:

ID	1​	2​	3​	4​	5​	6​
85435​	0​	7.5​	0​	7.5​	0​	0​
23546​	11​	0​	7.5​	0​	0​	37.5​
64254​	4​	9​	7.5​	0​	37.5​	0​

What I would like to achieve is for the types which are currently columns to be grouped and shown in detail next to the total amount against that id and type, below is the expected result from the data above:

ID	Type	Amount
85435​	2​	7.5​
85435​	4​	7.5​
23546​	1​	11​
23546​	3​	7.5​
23546​	6​	37.5​
64254​	1​	4​
64254​	2​	9​
64254​	3​	7.5​
64254​	5​	37.5​

If anyone has any tips or suggestions I'd massively appreciate it!

 

[hilfy]

Given the way your data is set up, you won't be able to use a cross-tab for this. Instead, you're going to have to use a Command to get the data in the right format. I would use a query like this:

Select
ID,
'1' as ValueType,
1 as Value
From MyTable
UNION ALL
Select
ID,
'2' as ValueType,
2
From MyTable
UNION ALL
...
UNION ALL
Select
ID,
'N' as ValueType,
N as Value
From MyTable

Where "N" is the highest number in the field list.

This will put the data into the format that you need. From there, you'll just set up a simple table with the ID, ValueType, and Value fields from your query. For more information about working with commands, see my blog post here: https://community.sap.com/t5/techno...g-commands-with-crystal-reports/ba-p/13142196.

-Dell