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


Split column rows


Split column rows

Author
Message
giszzmo
giszzmo
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 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
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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-Addicted
SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)SSC-Addicted (468 reputation)

Group: General Forum Members
Points: 468 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
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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
SSC Guru
SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)SSC Guru (60K reputation)

Group: General Forum Members
Points: 60983 Visits: 17954
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
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16369 Visits: 10109
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
SSC-Enthusiastic
SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)SSC-Enthusiastic (159 reputation)

Group: General Forum Members
Points: 159 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