How to convert ROW data into COLUMN data

In this article, I'll guide you on how to convert ROW data into COLUMN data.

I'll demonstrate how it is done in Oracle and SQL-Server using PIVOT functions.

For SQL Server version, please click on the following link: https://github.com/habdulsamed/SQLServer/issues/1#issue-734106803

Use Case:

In OLTP system, the Sales Data will be stored at the granular level like Sales Date and Sales Amount.

From this data, business will like rollup and summarize data for Year, Month.

Sometimes they want to see the Sales by Day wise to see if there is any particular day the sales are high.

Usually Sales will be high on Tue, Wed, Thu and little dull Fri and Mon.

Likewise DMV offices will be very crowded on Sat and Mon. So this kind of metrics are very popular. This logic can also be used to flatten data from some of the COTS (Commercially Off The Shelf) applications.

Let us create a table called tblSalesByDay that stores Sales for each day of the week at month level.

CREATE TABLE tblSalesByDay (
	Month        VARCHAR2 (50) NULL,
	DayOfWeek    VARCHAR2 (50) NULL,
	SalesAmount  NUMBER  (10, 3) NULL
)


INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Mon', 100.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Tue', 200.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Wed', 250.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Thu', 200.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Fri', 200.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Sat', 150.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Mon', 125.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Tue', 140.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Wed', 120.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Thu', 150.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Fri', 110.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Sat', 140.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Mon', 100.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Tue', 110.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Wed', 120.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Thu', 100.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Fri', 120.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Sat', 140.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Sun', 100.00);


COMMIT;

Now let us pivot the data to show the data by Day.

-- [Oracle] Query 1:
-- If you want to get only one aggregated measure like Sum of Sales by Day
SELECT * FROM tblSalesByDay t
PIVOT
(
  SUM (SalesAmount) FOR DayOfWeek IN ('Mon' AS "Mon", 'Tue' AS "Tue", 'Wed' AS "Wed", 'Thu' AS "Thu", 'Fri' AS "Fri", 'Sat' AS Sat, 'Sun' As Sun)
)
ORDER BY TO_DATE (Month, 'MON');

The same can be done without hardcoding using the below code snippet. This will be helpful if we want to pivot by Product Category, Product Sub-Category, Employee Type, Sales Channel, etc. This can be extended to any SQL and made fully dynamic as well.

Oracle PL/SQL anonymous block code snippet:

-- [Oracle] Query 2:
-- If you want to get only one aggregated measure like Sum of Sales by Day and want to get the output without hard-coding


SET SERVEROUTPUT ON


VARIABLE v_rc REFCURSOR;


DECLARE
  v_sql    VARCHAR2 (32767);
  v_fields VARCHAR2 (32767);
BEGIN
  SELECT LISTAGG ('''' || DayOfWeek || ''' AS "' || DayOfWeek || '"',  ',')
         WITHIN GROUP (ORDER BY DayOfWeek)
  INTO v_fields
  FROM (SELECT DISTINCT DayOfWeek FROM tblSalesByDay);
  
  v_sql := 'SELECT * FROM ' ||
           '(SELECT * FROM tblSalesByDay) ' ||
           'PIVOT (' ||
           ' MIN (SalesAmount) FOR DayOfWeek IN (' || v_fields || ')' ||
           ')' ||
           'ORDER BY  TO_DATE (Month, ''MON'')';


  --dbms_output.put_line (v_fields);
  --dbms_output.put_line (v_sql);
  OPEN :v_rc FOR v_sql;
END;
/
PRINT :v_rc

That's all folks. Hope it is useful.

Below is the SQL Server implementation of it.

CREATE TABLE [dbo].[tblSalesByDay]
(
  [Month]       [nvarchar](50) NULL,
  [DayOfWeek]   [nvarchar](50) NULL,
  [SalesAmount] [money] NULL
);


INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Mon', 100.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Tue', 200.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Wed', 250.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Thu', 200.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Fri', 200.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Jan', 'Sat', 150.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Mon', 125.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Tue', 140.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Wed', 120.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Thu', 150.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Fri', 110.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Feb', 'Sat', 140.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Mon', 100.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Tue', 110.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Wed', 120.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Thu', 100.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Fri', 120.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Sat', 140.00);
INSERT INTO tblSalesByDay (Month, DayOfWeek, SalesAmount) VALUES ('Mar', 'Sun', 100.00);


COMMIT;


-- [SQL Server] Query 1:
-- If you want to get only one aggregated measure like Sum of Sales by Day
SELECT * FROM tblSalesByDay t
PIVOT
(
  SUM (SalesAmount) FOR [DayofWeek] IN ([Mon], [Tue], [Wed], [Thu], [Fri], [Sat], [Sun])
) AS pivottable;


-- [SQL Server] Query 2:
-- If you want to get only one aggregated measure like Sum of Sales by Day and want to get the output without hard-coding


DECLARE @v_sql    NVARCHAR (4000);
DECLARE @v_fields NVARCHAR (4000);


BEGIN
  SELECT @v_fields = STRING_AGG ('[' + DayOfWeek + '] ', ',')
         WITHIN GROUP (ORDER BY DayOfWeek)
  FROM (SELECT DISTINCT DayOfWeek FROM tblSalesByDay) t;
  
  SET @v_sql = 'SELECT * FROM ' +
           '(SELECT * FROM tblSalesByDay) t ' +
           'PIVOT (' +
           ' MIN (SalesAmount) FOR DayOfWeek IN (' + @v_fields + ')' +
           ') AS pt' +
           ' ORDER BY  CAST (''01''+Month+''2020'' AS DATE)';


  print (@v_fields);
  print (@v_sql);


  EXEC sp_executesql @v_sql
END;

Below is the output from both the query execution.

Happy Learning and Problem Solving!!!