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.
>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