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
– Damien_The_Unbeliever, Commented Jan 4, 2016 at 8:34
No, it needs to be done in SQL Server. I need to generate a report and Export to Excel. Thanks. — Coding Bear
– Coding Bear, Commented Jan 4, 2016 at 8:38

Devart · Accepted Answer · 2016-01-04 08:11:50Z

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

answered Jan 4, 2016 at 8:11

Devart

120k23 gold badges172 silver badges189 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Mudassir Hasan Over a year ago

what does ORDER BY 1/0 mean ?

Devart Over a year ago

@HeartBleed, it's just a sort records in natural order... Check the similar question: stackoverflow.com/questions/4810627/…

Collectives™ on Stack Overflow

Increment columns when more than one record in table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related