在多列上分布任意行

2021-09-10 00:00:00 tsql sql-server data-entry

我有一张材料表.我需要填写该表中的数据输入表.

I have a table of materials. I need to fill a data entry form from that table.

问题是数据输入表单被分成多列，每列包含许多材料，如图所示.如何编写 tsql select 查询，将第一组材料名称放入一列，将第二组材料名称放入第二列，依此类推.

The problem is that the data entry form is divided into multiple columns each one containing a a number of materials as in the picture. How to write a tsql select query to obtain the first bunch of material names into a column, the second bunch into a second column and so on.

推荐答案

在客户端执行此操作可能最简单，而不是在数据库中执行此操作，但如果您真的想执行此操作，我会假设最简单的方法将行分成 3 组是使用 row_number() 模 3 并使用它构建单独的列.这将使行的顺序略有不同:

It might be easiest to do this in the client side, and not in the database, but if you really want to do this, I would assume the simplest way to distribute rows into 3 groups is to use row_number() with modulo 3 and build separate columns using that. That will order the rows slightly differently:

A    B    C
D    E    F
G    H

如果您需要按其他顺序排列，则需要将 row_number() 与行数除以 3.这样您就可以按顺序排列了

If you need to have them in the other order, then you need to divide the row_number() with the number of rows divided by 3. That will you get them in the order

A    D    G
B    E    H
C    F

示例:

select
  max(case when GRP = 0 then VALUE end),
  max(case when GRP = 1 then VALUE end),
  max(case when GRP = 2 then VALUE end)
from
(
    select 
      (row_number() over (order by VALUE)-1) % 3 as GRP, 
      (row_number() over (order by VALUE)-1) / 3 as ROW, 
      VALUE
    from table1
)X
group by ROW

SQL Fiddle

示例如何以另一种方式划分行:

Example how to divide the rows the other way:

declare @NOLINES int
select @NOLINES = ceiling(count(*) / 3.0) from table1

select
  max(case when GRP = 0 then VALUE end),
  max(case when GRP = 1 then VALUE end),
  max(case when GRP = 2 then VALUE end)
from
(
    select 
      (row_number() over (order by VALUE)-1) / @NOLINES as GRP, 
      (row_number() over (order by VALUE)-1) % @NOLINES as ROW, 
      VALUE
    from table1
)X
group by ROW

相关文章