Follow

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use
Contact

Combine every second row into single row

I have simple table1 with

columnA   columnB
A          10
A          20
B          15
B          50
C          2
C          40

Goal to see this results

columnA   columnB   Newcolumn
A          10         20
B          15         50  
C          2          40 

My query

MEDevel.com: Open-source for Healthcare and Education

Collecting and validating open-source software for healthcare, education, enterprise, development, medical imaging, medical records, and digital pathology.

Visit Medevel

SELECT * FROM   
(
    SELECT 
        ColumnA, ColumnB
    FROM 
       table1
        
) t 
PIVOT(
    COUNT(ColumnA) 
    FOR ColumnB IN (
        [Newcolumn],  - Not sure about this Newcolumn
     )
) AS pivot_table;

This is the error

Msg 8114, Level 16, State 1, Line 13
Error converting data type nvarchar to float.
Msg 473, Level 16, State 1, Line 13
The incorrect value "Newcolumn" is supplied in the PIVOT operator.
The incorrect value "newcolumne" is supplied in the PIVOT operator.

>Solution :

  • guaranteed to be ordered by column B:

    ;WITH src AS 
    (
      SELECT columnA, columnB, rn = ROW_NUMBER() 
        OVER (PARTITION BY columnA ORDER BY columnB)
      FROM dbo.table1
    )
    SELECT columnA, columnB = [1], newcolumn = [2]
    FROM src
    PIVOT 
    (
      MAX(columnB) FOR rn IN ([1],[2])
    ) AS p;
    

    Output:

    columnA columnB newcolumn
    A 10 20
    B 15 50
    C 2 40
  • arbitrary / "I don’t care":

    ;WITH src AS 
    (
      SELECT columnA, columnB, rn = ROW_NUMBER() 
        OVER (PARTITION BY columnA ORDER BY @@SPID)
      FROM dbo.table1
    )
    SELECT columnA, columnB = [1], newcolumn = [2]
    FROM src
    PIVOT 
    (
      MAX(columnB) FOR rn IN ([1],[2])
    ) AS p;
    

    Output here is the same, but that is just a coincidence:

    columnA columnB newcolumn
    A 10 20
    B 15 50
    C 2 40
  • example db<>fiddle

Add a comment

Leave a Reply

Keep Up to Date with the Most Important News

By pressing the Subscribe button, you confirm that you have read and are agreeing to our Privacy Policy and Terms of Use

Discover more from Dev solutions

Subscribe now to keep reading and get access to the full archive.

Continue reading