Divide column data into equal parts
-
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 intSELECT @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.
-
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 -
This was removed by the editor as SPAM
-
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 ShawFor 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 -
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 -
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 - drew.allen - Tuesday, January 9, 2018 2:10 PM
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? -
This was removed by the editor as SPAM
- Papil - Tuesday, January 9, 2018 1:58 PM
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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
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 - drew.allen - Wednesday, January 10, 2018 9:17 AM
Ah... yeah. Not enough coffee. I see it now. Thanks.
On the presentation layer thing, you're not sure how to do it in SSRS. That kind of negates it being a presentation layer problem for you. 😉 I agree that you could use a "Matrix" in SSRS for the final display but I'm thinking that the final display is the easy part. And, no... I don't know how to do the required unpivot in SSRS but a little integer math in T-SQL does it easily.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - subramaniam.chandrasekar - Monday, January 8, 2018 11:40 PM
Yeah... Please post some code that demonstrates how to do that. 😉
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) - Papil - Tuesday, January 9, 2018 1:58 PM
Ok, Papil. Let's get started.
I don't know if the verbal help you've gotten so far has helped or not. If it hasn't, then consider posting some "Readily Consumable" test data for people to use to demonstrate some code for you. Please see the first link in my signature line below for one way to do that. Here's another way using VALUES instead of SELECT/UNION ALL. Both work just fine for everything 2008 or better, doesn't take that long for you to do, and entices people to provide better help much more quickly.
So here's how to build the test data. This is NOT a part of the solution. We're just building test data to play with here.
--===== If the test table already exists, drop it make reruns in SSMS easier.
IF OBJECT_ID('tempdb..#TestTable','U') IS NOT NULL
DROP TABLE #TestTable
;
--===== Create and populate the test table on-the-fly.
SELECT *
INTO #TestTable
FROM (VALUES
('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')
) v (Column0,Column1,Column2,Column3,Column01,Column12,Column23,Column34)
;Now, the fun part. We need to do some row numbering with some INTEGER MATH (the % operator is a MODULO, which returns a remainder of division), unpivot the column pairs, and then repivot them in the order you want. As you found out, it seems incredibly difficult to do but a little bit of integer math goes a long way in making such a thing pretty simple. A lot of people say to do this type of thing in the "presentation layer" but then they frequently end up with either a T-SQL script or stored procedure being called from whatever they've chosen for a presentation layer. Done correctly, it just doesn't take that many clock cycles to accomplish and it's better than having some report writer beating the hell out of the system to try to do the same thing.
Here's the code for the solution. If you really do want NULLs to show up instead of spaces, then just change the ' ' stuff to NULL and Bob's your Uncle. 😉
WITH cteUnpivot AS
(--===== Unpivot and group the 2 sets of columns and number each set with a group number.
SELECT Col0Grp = (ROW_NUMBER() OVER (PARTITION BY Col0 ORDER BY ca.Col0)-1)/2
,ca.*
FROM #TestTable
CROSS APPLY
(--==== This does the unpivot of the two sets of columns.
SELECT Column0,Column1,Column2,Column3 UNION ALL
SELECT Column01,Column12,Column23,Column34
)ca (Col0,Col1,Col2,Col3)
)
,cteDetermineLeftOrRight AS
(--===== Figure out if each row will end up in the Left or Right set of columns.
SELECT LeftRight = (ROW_NUMBER() OVER (PARTITION BY Col0,Col0Grp ORDER BY Col0,Col0Grp)-1)%2
,*
FROM cteUnpivot
)--==== Use an good ol'' fashioned, easy to understand, high performance CROSSTAB to repivot the data.
-- You could do this in SSRS with a "Matrix" but I don't use SSRS because it's too slow for me.
-- As you can see here, the code is incredibly symetrical and "Copy'n'Paste" easy.
SELECT Column0 = MAX(CASE WHEN LeftRight = 0 THEN Col0 ELSE '' END)
,Column1 = MAX(CASE WHEN LeftRight = 0 THEN Col1 ELSE '' END)
,Column2 = MAX(CASE WHEN LeftRight = 0 THEN Col2 ELSE '' END)
,Column3 = MAX(CASE WHEN LeftRight = 0 THEN Col3 ELSE '' END)
,Column01 = MAX(CASE WHEN LeftRight = 1 THEN Col0 ELSE '' END)
,Column12 = MAX(CASE WHEN LeftRight = 1 THEN Col1 ELSE '' END)
,Column23 = MAX(CASE WHEN LeftRight = 1 THEN Col2 ELSE '' END)
,Column34 = MAX(CASE WHEN LeftRight = 1 THEN Col3 ELSE '' END)
FROM cteDetermineLeftOrRight
GROUP BY Col0,Col0Grp
;
Here are the results in T-SQL. Again, I don't know much about SSRS but I do know you can use it to call this script or put the script into a stored procedure and then call the stored procedure.
To understand how CROSSTABs work and their advantage over things like PIVOT, please see the following 2 articles. It's an "ancient" form of T-SQL "Black Arts" that everyone should learn.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns[/url]
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs[/url]If you have any questions, please don't hesitate to ask.
--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.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
This was removed by the editor as SPAM
- Jeff Moden - Wednesday, January 10, 2018 7:11 PM
It's a presentation issue, because it's only purpose is to change how the data is presented. I have a rough idea how to do this in SSRS, but I don't have SSRS installed anywhere to test it. It involves having nested tablixes. The outer one would enforce the grouping based on Column0 and the inner one would do the horizontal layout.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply