I have a detailed table, which has structure as below,
| Column Name | Data Type |
| Actor | Text |
| Action | Text |
| Events | Number |
The data stored in the table is as below,
| Node 1 | 3 | |
| Node 1 | Fax | 2 |
| Node 2 | Fax | 3 |
| Node 6 | 1 | |
Until my data was in Excel i was easily able to get the output as below, now i had transferred my backend to MSSQL and trying to get the similar output.
| Actor | Fax | ||
| Node 1 | 3 | 2 | |
| Node 2 | 3 | ||
| Node 6 | 1 |
Any help will be highly appreciated.
using this query
SELECT
Actor,
SUM(CASE WHEN (action='EMAIL') THEN 1 ELSE 0 END) AS Email,
SUM(CASE WHEN (action='PRINT') THEN 1 ELSE 0 END) AS Print,
SUM(CASE WHEN (action='FAX') THEN 1 ELSE 0 END) AS Fax
FROM
tablename
GROUP BY
Actor;Here, when we will use the aggregate function SUM it will add a reference column to the result, and the CASE phrase will parse the value of the action, bifurcating them into three different columns based on the specific values. As we are well aware of the distinct column value we can directly map the column values to the corresponding columns. Hence creating the PIVOT result format for the fetched data.
