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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1105 Visits: 411
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 Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64189 Visits: 17050
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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3516 Visits: 531
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 (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)SSC Guru (165K reputation)

Group: General Forum Members
Points: 165607 Visits: 21622
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1105 Visits: 411
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 Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64189 Visits: 17050
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
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1105 Visits: 411
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
Hall of Fame
Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)Hall of Fame (3.5K reputation)

Group: General Forum Members
Points: 3516 Visits: 531
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 (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)SSC Guru (872K reputation)

Group: General Forum Members
Points: 872841 Visits: 47501
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 Guru
SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)SSC Guru (64K reputation)

Group: General Forum Members
Points: 64189 Visits: 17050
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