将行分为12列 [英] Split rows into 12 columns

问题描述

我有一个表，有1000行.这些列之一就是URL.

I have a table with, say, 1000 rows. One of those columns is URL.

因此，从表中选择URL.

So, select URL from table.

但是我想每行显示12个URL.原因是我们正在发布供人们在线进行的工作，他们将一次审查12个网站.

However I want to display 12 URLs per row. The reason is that we are publishing work for people to do online and they will review 12 websites at a time.

因此，在这种情况下，我们将输出约90行，每行有12列(最后一行除外).

So in this case we would have about 90 rows being output, each having 12 columns (except the last row which will be short a few).

有没有简单的方法可以做到这一点?这是枢轴或其他功能的情况吗?

Is there a straightforward way to do this? Is this a case for pivot or some other function?

推荐答案

请尝试以下查询，它在Oracle 11G中有效:

Please try below query, it works in Oracle 11G:

SELECT * FROM(
  SELECT URL,
    CAST(FLOOR((NUM+2)/3) AS INT) AS Row_Num,
    MOD(NUM+2, 3)+1 AS OrderNum
  FROM(
      SELECT 
        ROW_NUMBER() OVER (ORDER BY URL) as NUM, URL
      FROM 
        TBL_URL
  )T1
)T2 PIVOT (MIN(URL) FOR OrderNum IN ('1' AS "1", '2' AS "2", '3' AS "3"));

这篇关于将行分为12列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！