Displaying variable across multiple columns

[RandallGraves]

Hi,

I have a result set that often contains identical data for each variable except one. Rather that return, for example, 10 rows of data where all the values are the same except for this field, I'm asked to dispay the unique variable across 10 columns (hope that made sense.) So, instead of having a result set of 20 columns and 10 rows, I'd now have one row and 29 columns (one column for each of the ten different values for the variable.) Is there a simple way to do this is DeskI? Thanks.

 

[jabedin]

Hi there

Would you be able to include an example.

 

[RandallGraves]

Thanks for the reply. Unfortunately, I don't have access to the data at the moment, so I'll try to provide a comparable (if much smaller) example.

Instead of a report that looks like this:

City State Order # Hobby
Miami FL 42 Golf
Miami FL 65 Golf
Seattle WA 17 Hiking
Seattle WA 29 Hiking

I need to create a report that looks like this:

City State Order #1 Order #2 Hobby
Miami FL 42 65 Golf
Seattle WA 17 29 Hiking

Where Order # is the variable that needs to be displayed horizontally rather than vertically. The number of orders for a given city and state can also vary. Thanks.

 

[blom0344]

Your request can be summed up as 'denormalization' data.
Instead of showing is row-by-row you want it in seperate columns.
The nitty gritty is that you need a counter from the dataset for this to work. You can then create a crosstab that displays Order# in the body with the counter in the column heading and the other dimensions in rows.

You cannot create such a counter within the report, but you can work one into the universe:

rownumber() OVER (PARTITION BY CITY,STATE,HOBBY ORDER BY ORDER#) AS Cnt

with add a counter that resets for new combinations of
CITY,STATE,HOBBY:

City      State     Order #     Hobby     Cnt
Miami     FL         42           Golf     1
Miami     FL         65           Golf     2
Seattle   WA        17           Hiking    1 
Seattle   WA        29           Hiking    2

Ties Blom

 

[skrandel]

Great solution!

You'll be able to present the data the way you want. You'll still return the EXACT same number of rows. It's not possible to denormalize the data at the SQL level. You don't know how wide it could be.

Steve Krandel
Symantec

 

[RandallGraves]

Thanks for the reply.

Not to complicate matters further, but if the counter will tend to vary depending on the date range selected by the user (in our earlier example, let's assume the counter for the Miami rows would be 24 through 27 while the counter for the Seattle rows would be 1 through 3) a crosstab will result in a large number of columns. Is there a way to reduce the number of columns created by, for example, capturing only the records with the highest 10 counter values (for each City) and placing them in the same 10 columns regardless of whether the highest counter values range from 1-10 or 100-109? Thanks.

 

[blom0344]

The counter resets itself for every combination of City, State, Hobby.
So for every combination it will have values starting with 1.
If you want only 10 results for every city, then create a second counter like:

rownumber() OVER (PARTITION BY CITY ORDER BY ORDER#) AS Cnt2

And filter then accordingly on this second counter dimension.

Ties Blom

 

[jabedin]

Hi,

You can try one simple way if that helps. Create a simple report with the variable you need. click on the report, right mouse click, format table, go to pivot. put your city,state under column, hobby under row and order under body. Let me know whether that meets your requiement.

 

[blom0344]

There are multiple values for order# for each given set of
City, State, Hobby.
That is why a simple pivottable will not work.
The counter that I describe offers a way to create unique combinations..

Ties Blom