Help us understand the problem. What is going on with this article?

pivot句のinにインライン埋め込めない(埋め込みたい)

More than 1 year has passed since last update.

ハードで数値リテラル列挙(したくない)

--divisor:19
--dividend:78

WITH sub AS (
    SELECT
        DENSE_RANK() OVER(
            PARTITION BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
            ORDER BY LEVEL
        ) - 1 AS grp
        ,DENSE_RANK() OVER(
            ORDER BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
        ) AS seq
        , LEVEL AS rn
    FROM
        dual
    CONNECT BY
        LEVEL <=:dividend
) SELECT
    *
  FROM
    sub
        PIVOT ( MAX ( rn )
            FOR seq
            IN (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19
            )
        )
ORDER BY
    grp
;

in句にインライン(埋め込みたい)

--divisor:19
--dividend:78

WITH sub AS (
    SELECT
        DENSE_RANK() OVER(
            PARTITION BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
            ORDER BY LEVEL
        ) - 1 AS grp
        ,DENSE_RANK() OVER(
            ORDER BY to_number(decode(MOD(LEVEL,:divisor), 0,:divisor, MOD(LEVEL,:divisor) ))
        ) AS seq
        , LEVEL AS rn
    FROM
        dual
    CONNECT BY
        LEVEL <=:dividend
) SELECT
    *
  FROM
    sub
        PIVOT ( MAX ( rn )
            FOR seq
            IN (
                SELECT
                    level
                FROM
                    dual
                CONNECT BY
                    level <=:divisor
                )
        )
ORDER BY
    grp
;

妥協して組み立てた。

--divisor:19
--dividend:78

with presni as(
select 'with sub as( select dense_rank() over (order by decode(mod(level,:divisor),0,:divisor,mod(level,:divisor))) - 1 as grp ,dense_rank() over (partition by decode(mod(level,:divisor),0,:divisor,mod(level,:divisor)) order by rownum) as seq,level as rn from dual connect by level <= :dividend )select * from sub pivot(min(rn) for seq in (' as pre_sni  from dual
),liz as(
select listagg(rn,',') within group (order by rownum) as liz from (select level as rn from dual connect by level <= :divisor)
),postsni as(
select ')) order by grp;' as post_sni from dual
)
select s1.pre_sni || s2.liz || s3.post_sni as build_sql from presni s1,liz s2,postsni s3;


あとがき

埋め込みたいなー。ただの遊びで気づいたので、記事upしました。。

妥協して組み立て直した。

こぴって実行したら、結果違って、ぇ!ってなったので、組み立てなおしました。。
しょーもなぃ。

--divisor:19
--dividend:500

WITH presni AS (
    SELECT
        'with sub as( select dense_rank() over (partition by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor))) order by level) - 1 as grp ,dense_rank() over (order by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor)))) as seq,level as rn from dual connect by level <= :dividend )select * from sub pivot(max(rn) for seq in ('
AS pre_sni
    FROM
        dual
), liz AS (
    SELECT
        LISTAGG(rn, ',') WITHIN GROUP(
            ORDER BY
                ROWNUM
        ) AS liz
    FROM
        (
            SELECT
                level AS rn
            FROM
                dual
            CONNECT BY
                level <=:divisor
        )
), postsni AS (
    SELECT
        ')) order by grp;' AS post_sni
    FROM
        dual
) SELECT
    s1.pre_sni
    || s2.liz
    || s3.post_sni AS build_sql
  FROM
    presni s1
    , liz s2
    , postsni s3;


with sub as( select dense_rank() over (partition by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor))) order by level) - 1 as grp ,dense_rank() over (order by to_number(decode(mod(level,:divisor),0,:divisor,mod(level,:divisor)))) as seq,level as rn from dual connect by level <= :dividend )select * from sub pivot(max(rn) for seq in (1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19)) order by grp;

image.png

ukijumotahaneniarukenia
sqlが大好き。(oracle) WordPressはじめました。sql oracleやそれ以外について調べたこととか記していきます。 https://github.com/ukijumotahaneniarukenia
https://ukijumotahaneniarukenia.site/
Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした