Home - General / All posts - split many item to new row every 13 items
 lionel571 post(s) #05-Aug-19 11:10 Is there a way in SQL for each list of values in all cells to align them and go to new row every 13 values ?it strange i shure than when use manifold 8 and excel import than manifold ask if the first row define column name and here in manifold 9 32 bit 169 no windows that ask this !! ok column first character can't be number character !! i ll test using javascript ......thank'sAttachments: excel_view.png liste.xlsx join image"Because my dad promised me" ( interstellar ) but blackhole don't exist best hardware with no ads focus on quality features price like manifold see xiaomi
 atrushwo59 post(s) #05-Aug-19 19:01 Given a table with two attributes ([VALUE] & [RANK]) where [VALUE] is your number and [RANK] is an arithmetic series from 0, the following code will pivot your data in Manifold 8.TRANSFORM SUM([VALUE])SELECT   FLOOR([RANK]/13)FROM ( SELECT    [VALUE],    [RANK],    [RANK] MOD 13 AS [MOD] FROM [TABLE] )GROUP BY FLOOR([RANK]/13)PIVOT [MOD]
 adamw8,776 post(s)online #07-Aug-19 12:29 Here is a quick and dirty attempt in 9 / Viewer:--SQL9 -- take a string of numbers, extract number NFUNCTION NumberNth(@s NVARCHAR, @n INT32) INT32 AS  CAST (StringRegexpSubstringNth(@s, @'\b\w+\b', @n, '') AS INT32)END; -- take a string of numbers, split into a table--  put numbers 0-1-2 into record 0--  put numbers 3-4-5 into record 1--  put numbers 6-7-8 into record 2FUNCTION SplitIntoNumbers(@s NVARCHAR) TABLE AS (  VALUES   (NumberNth(@s, 0), NumberNth(@s, 1), NumberNth(@s, 2)),   (NumberNth(@s, 3), NumberNth(@s, 4), NumberNth(@s, 5)),   (NumberNth(@s, 6), NumberNth(@s, 7), NumberNth(@s, 8))  AS (v0, v1, v2)) END; -- testSELECT SPLIT CALL SplitIntoNumbers([value])FROM (  VALUES    ('44 45 46 47 124'),    ('54 55 56 57 58 59 211')  AS ([value]));The comments should explain it.The above has two issues. First, we have to know the maximum number of numbers in the string in advance. Second, for strings that have less numbers, we are reporting rows full of NULL values. The second issue is easily fixed by doing WHERE v0 IS NOT NULL. The first issue is fixed much less easily - I think if I had to plan for arbitrarily long strings, I'd just rewrite SplitIntoNumbers as a script.
 lionel571 post(s) #09-Aug-19 10:38 thank's a lot for your code .In my case there is always 13 values for each 6 days !! but really nice to see that SQL is like script and custom function can be create so easely !!It ll be nice if import excel table can be achieve more easely ( don't deal with 32 64 bits hell) !!!! join image"Because my dad promised me" ( interstellar ) but blackhole don't exist best hardware with no ads focus on quality features price like manifold see xiaomi