Splitting with Delimiter and populating a Cross Tab Query

[helpme1986]

Hi Guys,

Ive been trying to convert some data that come out of a SharePoint Survey, its not the nicest and looks like this:

;#Answer 1;#Answer 2;#Answer 3;#

I would like a cross tab query that has a row header for each value between ";#" and then count them

e.g. if there are 2 lines of date like this:

;#Answer 1;#Answer 2;#Answer 3;#
;#Answer 1;#Answer 3;#

It would look like:

Answer 1 Answer 2 Answer 3
---- 2 ----- 1 ------- 2

Ive been trying to use the Split function but couldnt figure it out,

Hope this make sense,

 

[Peter Reid]

In your example, how do you know that there is no 'answer 2' in the second line of data?

 

[helpme1986]

Hi Peter,

Sharepoint builds the string based on the options the user selected, Answer 2 isnt in the string as the user didnt select it. If it had a field for each option it with a true or false value it would be much easier (and in a way thats what im trying to do)

 

[Peter Reid]

Admittedly, I'm not familiar with Sharepoint Survey output but if it is exported as you say, you won't be able to do what you want