Menu
SELECT * FROM EmployeeAttributes
PIVOT (
MAX(VALUE)
FOR AttributeID IN ([DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E],[BE8149E2-0806-4D59-8482-58223C2F1735],[23B2C459-3D30-41CA-92AE-7F581F2535D4])
) P
Result
EmployeeID DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E BE8149E2-0806-4D59-8482-58223C2F1735 23B2C459-3D30-41CA-92AE-7F581F2535D4
------------------------------------ -------------------------------------------------- -------------------------------------------------- --------------------------------------------------
329999EA-6288-4E7D-87E8-12FF865AB301 Rauf 23 10
34E2B762-F065-42BB-B4D8-2252102F1C20 Amal NULL
5
Now HOW can I change the column name to Name, Age, Salary respectively after the EmployeeID ?
589
You gotta change the name of columns for next Pivot Statement. You can use aggregate of pv3 to sum and group by the column you need. The key point here is that you create new category values by appending 1 or 2 to the end. Without doing this, the pivot query won't work properly.
The answer is no: PIVOT requires aggregation.
To change a column name, enter the following statement in your MySQL shell: ALTER TABLE table_name RENAME COLUMN old_column_name TO new_column_name; Replace table_name , old_column_name , and new_column_name with your table and column names.
Use AS to give a column alias.
SELECT EmployeeID,
[DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E] AS Name,
[BE8149E2-0806-4D59-8482-58223C2F1735] AS Age,
[23B2C459-3D30-41CA-92AE-7F581F2535D4] AS Salary
FROM EmployeeAttributes PIVOT ( MAX(VALUE) FOR AttributeID IN (
[DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E],
[BE8149E2-0806-4D59-8482-58223C2F1735],
[23B2C459-3D30-41CA-92AE-7F581F2535D4]) ) P
Perhaps easier solutions exist but placing the PIVOT statement result into a subquery allows you to alias the columns in the outer select.
SELECT EmployeeID = EmployeeID
, Name = [DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E]
, Age = [BE8149E2-0806-4D59-8482-58223C2F1735]
, Salary = [23B2C459-3D30-41CA-92AE-7F581F2535D4]
FROM (
SELECT *
FROM EmployeeAttributes
PIVOT (MAX(VALUE) FOR AttributeID IN (
[DD14C4C2-FC9E-4A2E-9B96-C6A20A169B2E]
, [BE8149E2-0806-4D59-8482-58223C2F1735]
, [23B2C459-3D30-41CA-92AE-7F581F2535D4])
) P
) P
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
