原表：
-------------- ----------- -----------------------
| Key | Field | Value |
-------------- ----------- -----------------------
| Foo | A | 1 |
| Foo | B | 2 |
| Foo | C | null |
| Foo | D | bar |
-------------- ----------- -----------------------
結果應該是：
-------------- --- --- ------ -----
| Key | A | B | C | D |
-------------- --- --- ------ -----
| Foo | 1 | 2 | null | bar |
-------------- --- --- ------ -----
我試過的：
SELECT
[Key] AS Id,
CASE WHEN Field = 'A' THEN Value END AS ACol,
CASE WHEN Field = 'B' THEN Value END AS BCol,
CASE WHEN Field = 'C' THEN Value END AS CCol,
CASE WHEN Field = 'D' THEN Value END AS DCol
FROM MyTable
GROUP BY [Key]
我越來越
Column 'Field' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Column 'Value' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
任何想法為什么？
uj5u.com熱心網友回復：
您需要取CASE運算式的最大值：
SELECT
[Key] AS Id,
MAX(CASE WHEN Field = 'A' THEN Value END) AS ACol,
MAX(CASE WHEN Field = 'B' THEN Value END) AS BCol,
MAX(CASE WHEN Field = 'C' THEN Value END) AS CCol,
MAX(CASE WHEN Field = 'D' THEN Value END) AS DCol
FROM MyTable
GROUP BY [Key];
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/381355.html
標籤：sql