Subscribe to this thread
Home - General / All posts - split many item to new row every 13 items
lionel

571 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's

Attachments:
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]

adamw


8,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 N

FUNCTION NumberNth(@s NVARCHAR, @n INT32INT32 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 2

FUNCTION SplitIntoNumbers(@s NVARCHARTABLE 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;

 

-- test

SELECT 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.

lionel

571 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

Manifold User Community Use Agreement Copyright (C) 2007-2019 Manifold Software Limited. All rights reserved.