Combine every second row into single row-CodePudding

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

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.

CodePudding user response：

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