Split Record by Field into Multiple Records
-
Hello all,
I have a table that I need to split up the records into multiple records by and Integer field. Here is what the table is like and what it should look like.
Thanks
/*** This is the Original Table that needs to be split into multiple records ***/
CREATE TABLE TestTable(SalesOrder VARCHAR(7), TotalMin INT)
INSERT INTO dbo.TestTable
( SalesOrder, TotalMin )
VALUES ('0164641', 1440 ),
('0191008', 737)
/*** This is the what the Final results should look like. ***/
CREATE TABLE FinalTable (SalesOrderNo VARCHAR(7),TotalMin INT)
INSERT INTO FinalTable
( SalesOrderNo, TotalMin )
VALUES ( '0164641', 360 ),
('0164641', 360),
('0164641',360),
('0164641',360),
('0191008', 360),
('0191008', 360),
('0191008',17)
-
You'll need to join to a numbers table
WITHE(n) AS(
SELECT n FROM (VALUES(0),(0),(0),(0),(0),(0),(0),(0),(0),(0))E(n)
),
E2(n) AS(
SELECT a.n FROM E a, E b
),
E4(n) AS(
SELECT a.n FROM E2 a, E2 b
),
cteTally(n) AS(
SELECT 0 UNION ALL
SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) n
FROM E4
)
SELECT t.SalesOrder,CASE WHEN c.n * 360 <= t.TotalMin THEN 360 ELSE t.TotalMin % 360 END AS TotalMin
FROM dbo.TestTable t
INNER JOIN cteTally c ON c.n BETWEEN 1 AND (t.TotalMin+359)/360;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Here's a solution using a table of numbers (you can create your own or use a virtual one) and my favorite TSQL word CASE. 🙂 Coffee hasn't kicked in and I am way down on sleep but I am certain there are other solutions, likely more efficient/cleaner.
;WITH a AS (SELECT SalesOrder, TotalMin, TotalMin/360.0 AS BatchCount, TotalMin%360 AS Remainder
FROM dbo.#TestTable)
SELECT SalesOrder,
CASE WHEN Remainder = 0 OR (Remainder > 0 AND n.Number < BatchCount) THEN 360
ELSE Remainder
END AS TotalMin
-- , * --so you can see how it works :-D
FROM a INNER JOIN KGBTools.dbo.Numbers n
ON n.number <= CASE WHEN Remainder = 0.0 THEN a.BatchCount
ELSE a.BatchCount + 1
END
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
Thanks Mark
-
Hello
What is the Kgb.Numbers table?
-
Hello Guys,
Thanks for the posts. Both worked great!
Thank you 🙂
-
Both Mark's and Kevin's solutions use a non-SARGable join. Here is a version where the join is SARGable. It also allows for variable length ranges. If you do set up variable length ranges, you have to make sure that they are contiguous and that they don't overlap.
;
-- Set up a small tally table.
WITH Tally(n) AS (
SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION)
FROM ( VALUES(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) v(n)
)
-- Set up the ranges. Here the ranges are equal, but they don't have to be.
, Ranges AS (
SELECT (n-1)*360 AS start_val, n*360 AS end_val
FROM Tally
)
-- Allows for variable lengths of ranges.
SELECT t.SalesOrder, CASE WHEN t.TotalMin < r.end_val THEN t.TotalMin % r.start_val ELSE r.end_val - r.start_val END
FROM #TestTable t
INNER JOIN Ranges r
ON t.TotalMin > r.start_val
;
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Chuck S (11/4/2016)
HelloWhat is the Kgb.Numbers table?
A Numbers table of course! 😛
1-n with clustered index on that column. Gets you the same thing as the virtual one using the nested CTEs that someone else posted in their example.
Lots of articles and examples on the web and here on SSC.com about what you can do with them. Most commonly used feature is probably delimited string splitting. Search this website for DelimitedSplit8K.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (11/4/2016)
Chuck S (11/4/2016)
HelloWhat is the Kgb.Numbers table?
A Numbers table of course! 😛
1-n with clustered index on that column. Gets you the same thing as the virtual one using the nested CTEs that someone else posted in their example.
Lots of articles and examples on the web and here on SSC.com about what you can do with them. Most commonly used feature is probably delimited string splitting. Search this website for DelimitedSplit8K.
Thanks for you help. 🙂
-
drew.allen (11/4/2016)
Both Mark's and Kevin's solutions use a non-SARGable join. Here is a version where the join is SARGable. It also allows for variable length ranges. If you do set up variable length ranges, you have to make sure that they are contiguous and that they don't overlap.;
-- Set up a small tally table.
WITH Tally(n) AS (
SELECT ROW_NUMBER() OVER(ORDER BY @@VERSION)
FROM ( VALUES(1), (1), (1), (1), (1), (1), (1), (1), (1), (1)) v(n)
)
-- Set up the ranges. Here the ranges are equal, but they don't have to be.
, Ranges AS (
SELECT (n-1)*360 AS start_val, n*360 AS end_val
FROM Tally
)
-- Allows for variable lengths of ranges.
SELECT t.SalesOrder, CASE WHEN t.TotalMin < r.end_val THEN t.TotalMin % r.start_val ELSE r.end_val - r.start_val END
FROM #TestTable t
INNER JOIN Ranges r
ON t.TotalMin > r.start_val
;
Drew
Thanks Drew! 🙂
-
Well done Drew. Especially nice given the added flexibility. 2 very minor improvements:
WITH Tally(n) AS (--more efficient "small" tally table :)
SELECT n
FROM ( VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) v(n)
)
-- Set up the ranges. Here the ranges are equal, but they don't have to be.
, Ranges AS (
SELECT (n-1)*360 AS start_val, n*360 AS end_val
FROM Tally
)
-- Allows for variable lengths of ranges.
--shortcut the CASE for the most frequent operation
SELECT t.SalesOrder,
CASE WHEN t.TotalMin >= r.end_val THEN r.end_val - r.start_val ELSE t.TotalMin % r.start_val END
FROM dbo.#TestTable t
INNER JOIN Ranges r
ON t.TotalMin > r.start_val;
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service -
TheSQLGuru (11/4/2016)
Well done Drew. Especially nice given the added flexibility. 2 very minor improvements:Thanks. I did consider including the first improvement, but decided to go with the less efficient one in case the OP had never seen a tally table.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
Another way (building off Drew and Kevin's excellent solutions) would be like this:
WITH iTally(n) AS(
SELECT n
FROM ( VALUES(1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) v(n)
)
SELECT
SalesOrder,
CASE WHEN n <= TotalMin/360 THEN 360 ELSE ISNULL(NULLIF(TotalMin%360,0),360) END
FROM dbo.TestTable
CROSS JOIN iTally
WHERE n <= (TotalMin/360 + SIGN(TotalMin%360));
This won't be any faster but, with a perm tally table you can easily turn this into an indexed view:
CREATE VIEW dbo.xxx WITH SCHEMABINDING AS
SELECT
SalesOrder,
xx = CASE WHEN n <= TotalMin/360 THEN 360 ELSE ISNULL(NULLIF(TotalMin%360,0),360) END
FROM dbo.TestTable
CROSS JOIN dbo.tally
WHERE n <= (TotalMin/360 + SIGN(TotalMin%360));
-- WHERE n BETWEEN 0 AND (TotalMin/360 + SIGN(TotalMin%360)); -- for perm tally tables that start at 0
GO
"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply