split data in equal halves
-
Hi,
I have source data as below. How can i split the data into equal halves and get the other half in the same result.Please suggest
Column0 Column1
P-1 A-1
P-2 A-2
P-3 A-3Output-
Column0 column1 Column2 Column3
P-1 A-1 P-3 A-3
P-2 A-2Thanks.
-
Are you talking about NTILE()?
SELECT BusinessEntityID
, JobTitle
, NTILE(4) OVER(ORDER BY BusinessEntityID) AS Quartile
,NTILE(2) OVER (ORDER BY BusinessEntityID) AS Half
FROM HumanResources.Employee;so Quartile will divide the set into 4 evenly-sized "chunks" and number them 1-4. You'd just use 2 instead of 4.
- pietlinden - Friday, February 1, 2019 6:51 PM
No i have 2 columns currently with 3 rows. But i want to split those rows equally and have 4 columns as shown above.
My data is not limited to just 3 rows. - Papil - Friday, February 1, 2019 5:17 PM
Can you describe the logic in more details please?
😎 -
here is an example, splitting the list of all your tables in your current database, into five columns;
i think you can adapt that to only have four columns, and change the source table to your own from there.
hope this helps!
since this is selecting from sys.tables, this should work for anyone as a prototype.--select count(*) from sys.tables
--1573 tables total
with baseCTE AS (select ROW_NUMBER() over (order by name) As RW,name from sys.tables),
--the / 25 limits me to 24 rows of 5 accross
firstCTE AS (select ROW_NUMBER() over (partition by RW / 5 order by name) as RW1, RW,name from baseCTE),
G1 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 1),
G2 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 2),
G3 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 3),
G4 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 4),
G5 AS (select ROW_NUMBER() over (order by name) As RW,RW1,name from firstCTE WHERE RW1 = 5)SELECT
G1.name,
G2.name,
G3.name,
G4.name,
G5.name
FROM G1
LEFT OUTER JOIN G2 ON G1.RW = G2.RW
LEFT OUTER JOIN G3 ON G1.RW = G3.RW
LEFT OUTER JOIN G4 ON G1.RW = G4.RW
LEFT OUTER JOIN G5 ON G1.RW = G5.RWLowell
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible! - Eirikur Eiriksson - Saturday, February 2, 2019 6:05 AM
Sorry for confusion.Here is DDL for input and output.
Splitting the data equally. If there are 3 rows. First two columns have 2 rows and next two column has 1 row and NULL. This example has only 3 rows. My table has more.
Input-
CREATE TABLE mytable1(
Column0 VARCHAR(3) NOT NULL PRIMARY KEY
,Column1 VARCHAR(3) NOT NULL
);
INSERT INTO mytable1(Column0,Column1) VALUES ('P-1','A-1');
INSERT INTO mytable1(Column0,Column1) VALUES ('P-2','A-2');
INSERT INTO mytable1(Column0,Column1) VALUES ('P-3','A-3');Output-
CREATE TABLE mytable(
Column0 VARCHAR(3) NOT NULL PRIMARY KEY
,column1 VARCHAR(3) NOT NULL
,Column2 VARCHAR(4)
,Column3 VARCHAR(4)
);
INSERT INTO mytable(Column0,column1,Column2,Column3) VALUES ('P-1','A-1','P-3','A-3');
INSERT INTO mytable(Column0,column1,Column2,Column3) VALUES ('P-2','A-2',NULL,NULL); - Papil - Saturday, February 2, 2019 7:14 PM
A little simple arithmetic and a CROSS TAB is all that's needed to solve this problem.
WITH cte AS
(
SELECT RN = (ROW_NUMBER() OVER (ORDER BY Column0, Column1)-1)/2
,CN = (ROW_NUMBER() OVER (ORDER BY Column0, Column1)-1)%2
,*
FROM #mytable1
)
SELECT Column0 = MAX(CASE WHEN CN = 0 THEN Column0 ELSE NULL END)
,Column1 = MAX(CASE WHEN CN = 0 THEN Column1 ELSE NULL END)
,Column2 = MAX(CASE WHEN CN = 1 THEN Column0 ELSE NULL END)
,Column3 = MAX(CASE WHEN CN = 1 THEN Column1 ELSE NULL END)
FROM cte
GROUP BY RN
;Results...
I say "simple" because if you really want the items in Column0/Column1 to be exhausted in order prior to the order resuming in Column2/Column3, that'll take a bit more. Let us know if you actually need it that way.
--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) -
Never mind... I did it the other way, too.
-- DROP TABLE #MyHead
--==========================================================================
-- Create the test data
-- (This is NOT a part of the solution)
--==========================================================================
CREATE TABLE #MyHead
(
Column0 VARCHAR(3) NOT NULL PRIMARY KEY
,Column1 VARCHAR(3) NOT NULL
)
;
INSERT INTO #MyHead(Column0,Column1)
VALUES ('P-1','A-1')
,('P-2','A-2')
,('P-3','A-3')
,('P-4','A-4')
,('P-5','A-5')
--,('P-6','A-6') --Just a test for an even number of items
;
--==========================================================================
-- Create the display where column set #1 is exhausted by sort order
-- and is continued into column set #2.
--==========================================================================
WITH
cteColumnSets AS
(--==== Enumerate the column "sets"
SELECT CN = NTILE(2) OVER (ORDER BY Column0, Column1)
,*
FROM #MyHead
)
,cteRowSets AS
(
--==== Enumerate the row "sets"
SELECT RN = ROW_NUMBER() OVER (PARTITION BY CN ORDER BY Column0,Column1)
,*
FROM cteColumnSets
)
SELECT Column0 = MAX(CASE WHEN CN = 1 THEN Column0 ELSE NULL END)
,Column1 = MAX(CASE WHEN CN = 1 THEN Column1 ELSE NULL END)
,Column2 = MAX(CASE WHEN CN = 2 THEN Column0 ELSE NULL END)
,Column3 = MAX(CASE WHEN CN = 2 THEN Column1 ELSE NULL END)
FROM cteRowSets
GROUP BY RN
;
Results:--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) -
Thanks .It works.
- Papil - Monday, February 4, 2019 7:10 AM
To be sure, do you understand how and why it works?
--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)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply