sql - 根据奇数和偶数将 2 列分为 4

Question

我想根据奇数和偶数将 2 列分成 4 列。我有一张桌子

INSERT INTO @tab
VALUES ('test', 'desc'), ('test1', 'desc1'), ('test2', 'desc2'),
       ('test3', 'desc3'), ('test4', 'desc4')

我尝试了以下查询：-

SELECT  
    CASE 
       WHEN A.RNK  % 2 = 1 
          THEN Heading 
    END AS Headingodds,
    CASE 
       WHEN A.RNK  % 2 = 0 
          THEN Heading
    END AS Headingeven,
    CASE 
       WHEN A.RNK  % 2 = 1 
          THEN A.[Description] 
    END AS Descriptionodds, 
    CASE
       WHEN A.RNK  % 2 = 0 
          THEN A.[Description]
    END AS Descriptioneven
FROM
    (SELECT 
         Id, Heading, [Description],
         ROW_NUMBER() OVER (ORDER BY Id) AS RNK
     FROM
         @tab) A

我从查询中得到的结果是：-

但我想要以下格式的结果：-

DECLARE @tab1 TABLE(Headingodd VARCHAR(100), Headingeven VARCHAR(100), Descriptionodd VARCHAR(100), Descriptioneven VARCHAR(100))

INSERT INTO @tab1 
VALUES ('test', 'test1', 'desc', 'desc1'),
       ('test2', 'test3', 'desc2', 'desc3'),
       ('test4', null, 'desc4', null)

SELECT * FROM @tab1

标签： sqlsql-serversql-server-2012

Answer 1

其他答案似乎很复杂。这实际上只是聚合和算术：

select min(case when rnk % 2 = 1 then heading end) as heading_odd,
       min(case when rnk % 2 = 0 then heading end) as heading_even,
       min(case when rnk % 2 = 1 then description end) as description_odd,
       min(case when rnk % 2 = 0 then description end) as description_even
from (select t.*, row_number() over (order by id) as rnk
      from tab t
     ) t
group by (rnk - 1) / 2
order by min(rnk);

这是一个 db<>fiddle。

rnk注意：如果您知道它id是连续的，没有间隙，您甚至不需要。