Increment columns when more than one record in table

Question

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.

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

Is the intention that, if there were a third row for `amdb_ma_se_1234`, there would be 7 columns rather than 5 and with a `_c` suffix? If so, this isn't easily done in SQL (where any given query has a fixed "shape" - the number of columns, names and types) - is it not an option to do this at the presentation layer? — Damien_The_Unbeliever, Jan 04 '16 at 08:34
No, it needs to be done in SQL Server. I need to generate a report and Export to Excel. Thanks. — Coding Bear, Jan 04 '16 at 08:38

score 1 · Accepted Answer · answered Jan 04 '16 at 08:11

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

@HeartBleed, it's just a sort records in natural order... Check the similar question: http://stackoverflow.com/questions/4810627/sql-server-2005-row-number-without-order-by — Devart, Jan 04 '16 at 09:31

Increment columns when more than one record in table

1 Answers1