Split column rows

Question

Split column rows

giszzmo

SSChasing Mays

Points: 647
More actions
May 8, 2014 at 9:54 am

#291132

I have a column that has two row values, "Big" and "Small." Is there a way to make two columns from it, one that will only have "Big" and then other with only the values, "Small"? I need to do this because I want to make a drilldown report where the "Big" column will open up to reveal the "Small" column. And I'm using three SharePoint Lists. Please let me know. Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

giszzmo SSChasing Mays Points: 647 More actions · Answer 1

Actually I think I would just filter out the values with an expression. Now, I'm not sure which expression. I don't think and IIF would work. What would the expression be to just show a certain value only?

Mohamed I. SSCrazy Points: 2774 More actions · Answer 2

Hi

How is your column data joined ? with a "space" or with a "coma"?

You can use the split() function:

split(<your field data>,<your separator>).GetValue(<index>).ToString

For example if the field contains "Big,Small"

To get the first value "Big"

You can do a split("Big,Small",",").getValue(0).ToString --Not sure if the index begins with 0 or 1 / have to test it

:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

giszzmo SSChasing Mays Points: 647 More actions · Answer 3

Neither. They are alternating values. One cell will say "Big" then the next cell may say "Small." So I was wanting to make two columns where the expression in them is the same except for a small part of it that will return only a specified value or either "Big" or "Small." Is there an expression that will do that?

Mohamed I. SSCrazy Points: 2774 More actions · Answer 4

Can you put a sample data please ?

And then what you want the result to be.

:w00t: !!!GOOGLE IS YOUR BEST FRIEND!!! :w00t:

giszzmo SSChasing Mays Points: 647 More actions · Answer 5

Current Column

Big

Small

Big

Small

Big

Small

Big

Small

I would like to have:

Column 1

Big

Column 2

Small

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

giszzmo (5/9/2014)
Current Column
Big
Big
Big
Small
Small
Big
Small
Small
Big
Big
Small
Big
Small
I would like to have:
Column 1
Big
Big
Big
Big
Big
Big
Big
Column 2
Small
Small
Small
Small
Small
Small

That doesn't exactly provide much detail. It certainly doesn't give anybody enough to help write a query. Please take a few minutes and read the first link in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

David Burrows SSC Guru Points: 65142 More actions · Answer 7

You could use an expression in column 1 of the tablix to test for Big and another in column 2 to test for Small

This would give you

Big

Small

Big

Small

Big

Small

Big

Small

Or you could put a tablix in column 1 and filter the dataset for Big and repeat for column 2 for Small

Far away is close at hand in the images of elsewhere.
Anon.

giszzmo SSChasing Mays Points: 647 More actions · Answer 8

Thanks. I ended up doing the second option with the filter.