Pivoting a table with one row in SQL

user_14010111 0
Viewed 34

I have a weekly report where I have to share our performance. I have the following query

SELECT COUNT(*) AS Transactions,
COUNT(DISTINCT id) as Customers,
SUM(CASE WHEN Product in ('Paper','Pen','Eraser') THEN 1 ELSE 0 END) School_Supplies,
SUM(CASE WHEN Product in ('Paper','Pen','Eraser') THEN Payment ELSE 0 END) AS School_Supplies_Revenue
FROM dbo.Transactions

Which gives me the table

Transactions | Customers | School_Supplies | School_Supplies_Revenue
12845        |  9654     |  1546           |  8745

I would like the table to pivot for it to look like this

KPI                      | Value
Transactions             | 12845
Customers                | 9654
School_Supplies          | 1546
School_Supplies_Revenue  | 8745

I tried using UNPIVOT, but it did not work. How can I pivot this table so the column names and their respective values become data points?

My database looks like this:

[ID] | [Product] | [Cost per Item] | [Payment] | [Transaction Date]
001  | Paper     |  2.15           |  5.54     | 2021-01-12
sql-server sql
1 Answers

In SQL Server, the simplest method might be apply:

SELECT v.*
FROM (SELECT COUNT(*) AS Transactions,
             COUNT(DISTINCT id) as Customers,
             SUM(CASE WHEN Product in ('Paper','Pen','Eraser') THEN 1 ELSE 0 END) as School_Supplies,
            SUM(CASE WHEN Product in ('Paper','Pen','Eraser') THEN Payment ELSE 0 END) AS School_Supplies_Revenue
      FROM dbo.Transactions t
     ) t CROSS APPLY
     (VALUES ('Transactions', Transactions),
             ('Customers', customers),
             ('School_Supplies', School_Supplies),
             ('School_Supplies_Revenue', School_Supplies_Revenue)
     ) v(kpi, value);
user_11440350
Related