SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Split column rows


Split column rows

Author
Message
giszzmo
giszzmo
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 124
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.
giszzmo
giszzmo
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 124
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.
Mohamed I.
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 348
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>Wink.GetValue(<index>Wink.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
giszzmo
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 124
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.
Mohamed I.
SSC-Enthusiastic
SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)SSC-Enthusiastic (192 reputation)

Group: General Forum Members
Points: 192 Visits: 348
Can you put a sample data please ?
And then what you want the result to be.

w00t !!!GOOGLE IS YOUR BEST FRIEND!!! w00t
giszzmo
giszzmo
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 124
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
Sean Lange
Sean Lange
One Orange Chip
One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)One Orange Chip (26K reputation)

Group: General Forum Members
Points: 26323 Visits: 17554
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.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
David Burrows
David Burrows
SSCrazy Eights
SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)SSCrazy Eights (9.7K reputation)

Group: General Forum Members
Points: 9665 Visits: 9762
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
Big
Big
Small
Small
Big
Small
Small
Big
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
giszzmo
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 124
Thanks. I ended up doing the second option with the filter.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search