8 Replies Latest reply: Oct 17, 2012 11:08 AM by Ewelina Eggert RSS

    How to concatenate values in one of the dimention in Pivot

      Hello, could you please help me to solve the problem:
      I have pivot table that contain list of customers with details like company name, phone number, address etc. and it shows in crosstab columns brands used by customer. Unfortunately for one customer we can have more than one phone number and then I have a problem - I should show all numbers, but concatenated into one cell.
      Now I have something like this:
      Company namePhone numberAddressBrand 1Brand 2Brand 3
      Company 1111 111 111 address 1xx
      Company 1111 222 111address 1xx
      Company 1111 111 333address 1xx
      Company 2122 123 123address 2xx
      Company 3222 222 222address 3xx
      Company 3222 222 111address 3xx
      and I would like to achieve something like this:
      Company namePhone numberAddressBrand 1Brand 2Brand 3
      Company 1111 111 111 ; 111 222 111; 111 111 333address 1xx
      Company 2122 123 123address 2x
      Company 3222 222 222; 222 222 111address 3xx
      On the straight table I could use concatenate function on Phone number and define it as Expression not as dimention. Unfortunately my Brand information is stored in the table like (Brand, Company) and phone numbers are in similar data structure - in both cases I have relationships one company to many (brands or phone numbers).
      How I can achieve what I need?