Concatenating Multiple String Values for a Summary Field in Cross Tab

Hello All,

I am attempting to concatenate or show multiple values of an intersection between a row and column. Since the data will be dynamic, it sounds like using a Cross Tab and data cube would be best in achieving this.
 
In the attached screenshot below, I'm using State and Size as a grouping in associated rows and columns, with Person as the Summary (intersection between the two).
 
I'm able to pull in a String value using the FIRST function, however I'm trying to show multiple values in that Summary Section and it will not show all of them. It's as if I want to CONCATENATE or aggregate those 3 person names, but there is no such function to use....

In the attached screenshot, I have my data set as such w/ the intended output below.
 
However, in attempting this. For that first cell (Between State A and Small) it will only show John.
How can one create a table or show all the information as such? Is there another way to display the information as intended?

Find more posts tagged with

Comments

shamo

what I can think of is to use a normal table. create 3 computed columns for small large and medium. then group on state. That will give you what you are looking for.

futbolfrank

Well, there actually will be a dynamic number of columns and values (Not just Small, Medium, Large) It could be X-Small, XXS, XL, XX-Large and the differing sizes could range from 1 type to 100 type of "sizes" depending on the data set. With that dynamic set (both in the columns and rows), how could/would it be best to represent it in a table? Would it be possible to do multiple cells inside the intersection and not concatenation alternatively?
 
Thx again

shamo

Crosstab wouldn't work since the summarized field to my best knowledge can only be numbers and not strings. Best way I know is what I've suggested. it appears the sizes definitely will have a finite number that isn't going to be more than 15. you can create computed columns to accommodate them. sorry if I haven't been of help to you.

pricher

Hi,
 
Here's an option that may work for you.
 
Assuming a Classic Models based query that returns a result set similar in structure to the one you provided, where COUNTRY is the row grouping and LASTNAME is the column grouping:

futbolfrank

Hi Pierre,
Thanks for the insight and potential solution/process to enable multiple text values to show up.
 
Instead of using the computed column method, I realized I could use a function that we had already installed and added in our MS SQL Server/database. This function groups and concatenates according to my first post. Which allows the Cross-Tab to function
 
This is not Out of the Box, but you can see the project page, installation files, and additional info here:
<a data-ipb='nomediaparse' href='http://groupconcat.codeplex.com/'>http://groupconcat.codeplex.com/</a>