問題描述

我有一張材料表.我需要填寫該表中的數(shù)據(jù)輸入表.

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

問題是數(shù)據(jù)輸入表單被分成多列，每列包含許多材料，如圖所示.如何編寫 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.

推薦答案

在客戶端執(zhí)行此操作可能最簡單，而不是在數(shù)據(jù)庫中執(zhí)行此操作，但如果您真的想執(zhí)行此操作，我會假設最簡單的方法將行分成 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() 與行數(shù)除以 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

這篇關于在多列上分布任意行的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！