当表中有多个记录时增加列

时间：2022-07-21 01:58:53

I am new to this sort of problem. I have tried looking and couldn't find anything. I also tried using Case expressions that increments each time but that was not working.

我是这类问题的新手。我试过看,找不到任何东西。我也尝试过使用Case表达式,每次都会递增,但这样做不起作用。

tblHipChop

hop    acp      ID
20     20       amdb_ma_se_1234
350    30       amdb_ma_se_1234

Must return the following:

必须返回以下内容:

hop_a    hcp_b    acp_a     acp_b     ID
20       350      20        30        amdb_ma_se_1234

1 个解决方案

#1

1  

DECLARE @tblHipChop TABLE (hop INT, [acp] INT, ID VARCHAR(50))

INSERT INTO @tblHipChop
VALUES (20, 20, 'amdb_ma_se_1234'), (350, 30, 'amdb_ma_se_1234')

SELECT
    hop_a = MAX(CASE WHEN t.RowNum = 1 THEN hop END),
    hcp_b = MAX(CASE WHEN t.RowNum = 2 THEN hop END),
    acp_a = MAX(CASE WHEN t.RowNum = 1 THEN [acp] END),
    acp_b = MAX(CASE WHEN t.RowNum = 2 THEN [acp] END),
    ID
FROM (
    SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 1/0)
    FROM @tblHipChop
) t
GROUP BY t.ID

output - 

hop_a       hcp_b       acp_a       acp_b       ID
----------- ----------- ----------- ----------- -----------------
20          350         20          30          amdb_ma_se_1234

check about - ROW_NUMBER

检查 - ROW_NUMBER

#1

1  

DECLARE @tblHipChop TABLE (hop INT, [acp] INT, ID VARCHAR(50))

INSERT INTO @tblHipChop
VALUES (20, 20, 'amdb_ma_se_1234'), (350, 30, 'amdb_ma_se_1234')

SELECT
    hop_a = MAX(CASE WHEN t.RowNum = 1 THEN hop END),
    hcp_b = MAX(CASE WHEN t.RowNum = 2 THEN hop END),
    acp_a = MAX(CASE WHEN t.RowNum = 1 THEN [acp] END),
    acp_b = MAX(CASE WHEN t.RowNum = 2 THEN [acp] END),
    ID
FROM (
    SELECT *, RowNum = ROW_NUMBER() OVER (PARTITION BY ID ORDER BY 1/0)
    FROM @tblHipChop
) t
GROUP BY t.ID

output - 

hop_a       hcp_b       acp_a       acp_b       ID
----------- ----------- ----------- ----------- -----------------
20          350         20          30          amdb_ma_se_1234

check about - ROW_NUMBER

检查 - ROW_NUMBER

标签：

相关文章