-4

Trying to find a solution, but nothings useful so far. I'm trying to pivot SQL table from this format:

+-----------+-------+--------+
| ProductID | Price | Client |
+-----------+-------+--------+
| bread     |    12 | A      |
| bread     |    13 | B      |
| bread     |    14 | C      |
| bread     |    15 | E      |
| sodapop   |     5 | A      |
| sodapop   |     6 | B      |
| sodapop   |     7 | C      |
| sodapop   |     8 | D      |
+-----------+-------+--------+

to this

+-----------+----------------+----------------+----------------+----------------+--+
| ProductID | Client A Price | Client B Price | Client C Price | Client D Price |  |
+-----------+----------------+----------------+----------------+----------------+--+
| bread     |             12 |             13 |             14 |             15 |  |
| sodapop   |              5 |              6 |              7 |              8 |  |
+-----------+----------------+----------------+----------------+----------------+--+
Dale K
  • 25,246
  • 15
  • 42
  • 71
default_settings
  • 440
  • 1
  • 5
  • 10
  • 2
    What have you tried so far? Are the clients fixed? You have client E in your source data but not in your results. – Stu Mar 11 '21 at 17:38
  • I have tried the provided solutions, unfortunately I got 2 errors: Msg 8114, Level 16, State 1, Line 74 Error converting data type nvarchar to float. Msg 473, Level 16, State 1, Line 74 The incorrect value "Column1" is supplied in the PIVOT operator. I've done the "CAST(Price AS FLOAT) AS Price" but doesn't solves the issue. Also tested SELECT MAX(Price) FROM #temptable, which is working. – default_settings Mar 12 '21 at 10:30

1 Answers1

2

the pivot syntax would be something like this :

SELECT * 
FROM 
( SELECT * FROM parts) src
PIVOT  ( MAX(Price) FOR CLient IN (A,B,C,D,E)
) AS pvt
eshirvana
  • 23,227
  • 3
  • 22
  • 38
  • I have tried the provided solutions, unfortunately I got 2 errors: Msg 8114, Level 16, State 1, Line 74 Error converting data type nvarchar to float. Msg 473, Level 16, State 1, Line 74 The incorrect value "Column1" is supplied in the PIVOT operator. I've done the "CAST(Price AS FLOAT) AS Price" but doesn't solves the issue. Also tested SELECT MAX(Price) FROM #temptable, which is working. – default_settings Mar 12 '21 at 15:35