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


Divide column data into equal parts


Divide column data into equal parts

Author
Message
Papil
Papil
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 252
I want to split one of the column's data into 8 equal parts.
For eg. i have 73 rows currently in one of column of table temp. I want to get 9-10 rows in every sql statement. I tried below query it splits the whole table data in two parts. But i want to split in 8 parts -(73/8). A sql cursor would be helpful in order to make it more dynamic. because the numbers of the rows could increase but i still want it to split in 8 parts only.

    DECLARE @CountOf int,@Top int,@Bottom int

SELECT @CountOf=COUNT(*) FROM temp

SET @Top=@CountOf/2

SET @Bottom=@CountOf-@Top

SELECT TOP (@Top) * FROM temp ORDER BY 1 asc

SELECT TOP (@Bottom) * FROM temp ORDER BY 1 desc


please help.

Thanks.
drew.allen
drew.allen
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45293 Visits: 14686
OFFSET/FETCH was introduced in SQL 2012 specifically to handle paging, which is what you are trying to do.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Subramaniam Chandrasekar
Subramaniam Chandrasekar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 493
Papil - Wednesday, January 3, 2018 2:52 PM
I want to split one of the column's data into 8 equal parts.
For eg. i have 73 rows currently in one of column of table temp. I want to get 9-10 rows in every sql statement. I tried below query it splits the whole table data in two parts. But i want to split in 8 parts -(73/8). A sql cursor would be helpful in order to make it more dynamic. because the numbers of the rows could increase but i still want it to split in 8 parts only.

    DECLARE @CountOf int,@Top int,@Bottom int

SELECT @CountOf=COUNT(*) FROM temp

SET @Top=@CountOf/2

SET @Bottom=@CountOf-@Top

SELECT TOP (@Top) * FROM temp ORDER BY 1 asc

SELECT TOP (@Bottom) * FROM temp ORDER BY 1 desc


please help.

Thanks.

Please use ROW_Count() OR OFFSET FETCH for your purpose.

ChrisM@Work
ChrisM@Work
SSC Guru
SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)SSC Guru (118K reputation)

Group: General Forum Members
Points: 118565 Visits: 21111
If it isn't paging you are after, then consider NTILE(8) OVER()
NTILE()

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
Papil
Papil
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 252
Can someone pls help with the following data. currently my data is as below in the first table. How can i get it to split by column 0 values so as to get it as in the table 2.
Both table has 8 columns.
Thanks.Any leads will be appreciated.

Column0 Column1 Column2 Column3 Column01 Column12 Column23 Column34
A test1 test2 test3 C test1 test2 test3
A test1 test2 test3 C test1 test2 test3
A test1 test2 test3 C test1 test2 test3
A test1 test2 test3 C test1 test2 test3
B test1 test2 test3 C test1 test2 test3
B test1 test2 test3 C test1 test2 test3
B test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3

Column0 Column1 Column2 Column3 Column01 Column12 Column23 Column34
A test1 test2 test3 A test1 test2 test3
A test1 test2 test3 A test1 test2 test3
B test1 test2 test3 B test1 test2 test3
B test1 test2 test3 Null Null Null Null
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 Null Null Null Null

drew.allen
drew.allen
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45293 Visits: 14686
This appears to be a presentation issue and is best handled in the presentation layer. Is there a compelling reason for trying to do this in the database layer?

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Papil
Papil
SSC-Addicted
SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)SSC-Addicted (444 reputation)

Group: General Forum Members
Points: 444 Visits: 252
drew.allen - Tuesday, January 9, 2018 2:10 PM
This appears to be a presentation issue and is best handled in the presentation layer. Is there a compelling reason for trying to do this in the database layer?

Drew

need to get all the A first then B then C so as the SSRS shows them correctly column0 value wise.
Can i query the data from table 1 so that its replaced with table 2 as output?

Subramaniam Chandrasekar
Subramaniam Chandrasekar
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1698 Visits: 493
Papil - Tuesday, January 9, 2018 2:52 PM
drew.allen - Tuesday, January 9, 2018 2:10 PM
This appears to be a presentation issue and is best handled in the presentation layer. Is there a compelling reason for trying to do this in the database layer?

Drew

need to get all the A first then B then C so as the SSRS shows them correctly column0 value wise.
Can i query the data from table 1 so that its replaced with table 2 as output?

Please explain a bit on your query ? what output are you exactly trying to get using SSRS ?

Jeff Moden
Jeff Moden
SSC Guru
SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)SSC Guru (619K reputation)

Group: General Forum Members
Points: 619447 Visits: 45201
Papil - Tuesday, January 9, 2018 1:58 PM
Can someone pls help with the following data. currently my data is as below in the first table. How can i get it to split by column 0 values so as to get it as in the table 2.
Both table has 8 columns.
Thanks.Any leads will be appreciated.

Column0 Column1 Column2 Column3 Column01 Column12 Column23 Column34
A test1 test2 test3 C test1 test2 test3
A test1 test2 test3 C test1 test2 test3
A test1 test2 test3 C test1 test2 test3
A test1 test2 test3 C test1 test2 test3
B test1 test2 test3 C test1 test2 test3
B test1 test2 test3 C test1 test2 test3
B test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3

Column0 Column1 Column2 Column3 Column01 Column12 Column23 Column34
A test1 test2 test3 A test1 test2 test3
A test1 test2 test3 A test1 test2 test3
B test1 test2 test3 B test1 test2 test3
B test1 test2 test3 Null Null Null Null
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 C test1 test2 test3
C test1 test2 test3 Null Null Null Null


It's probably just me but I'm not seeing what the conversion rules are for this.

--Jeff Moden

RBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair

Helpful Links:
How to post code problems
How to post performance problems
Forum FAQs
drew.allen
drew.allen
SSC-Forever
SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)SSC-Forever (45K reputation)

Group: General Forum Members
Points: 45293 Visits: 14686
He has a columnar report that fills the columns top-to-bottom then left-to-right. He wants to change the order of the fill to left-to-right then top-to-bottom.

This is a presentation issue, as I previously stated, and should be handled in the presentation layer (SSRS). It's been awhile since I worked with SSRS, so I'm not sure how to handle this in SSRS.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
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