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;

```

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

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

```--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;

``` 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