Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Split column rows Expand / Collapse
Author
Message
Posted Thursday, May 8, 2014 9:54 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:54 AM
Points: 26, Visits: 57
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.
Post #1568999
Posted Thursday, May 8, 2014 10:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:54 AM
Points: 26, Visits: 57
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?
Post #1569019
Posted Friday, May 9, 2014 7:48 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:41 AM
Points: 41, Visits: 165
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
Post #1569273
Posted Friday, May 9, 2014 7:55 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:54 AM
Points: 26, Visits: 57
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?
Post #1569281
Posted Friday, May 9, 2014 8:01 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Friday, June 6, 2014 7:41 AM
Points: 41, Visits: 165
Can you put a sample data please ?
And then what you want the result to be.
Post #1569288
Posted Friday, May 9, 2014 9:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:54 AM
Points: 26, Visits: 57
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
Post #1569357
Posted Friday, May 9, 2014 9:59 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 9:10 AM
Points: 13,230, Visits: 12,709
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 Moden's 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)
Post #1569362
Posted Friday, May 9, 2014 10:25 AM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 8:50 AM
Points: 7,006, Visits: 7,177
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.

Post #1569374
Posted Friday, May 9, 2014 10:41 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:54 AM
Points: 26, Visits: 57
Thanks. I ended up doing the second option with the filter.
Post #1569383
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse