Convert Row Data into Column Data

     As usual let us demonstrate this using an example. We will create two tables and populate it with some data for our example. Tables are not normalized but they are created just to represent the scenario.
CREATE TABLE PRODUCT_COL
(
PRODUCT_ID INT IDENTITY(1,1) NOT NULL,
PRODUCT_DESC VARCHAR(50),
Q1_SALES_AMT NUMERIC(13,2) DEFAULT 0 NOT NULL,
Q2_SALES_AMT NUMERIC(13,2) DEFAULT 0 NOT NULL,
Q3_SALES_AMT NUMERIC(13,2) DEFAULT 0 NOT NULL,
Q4_SALES_AMT NUMERIC(13,2) DEFAULT 0 NOT NULL,
CONSTRAINT PK_PRODUCT_COL PRIMARY KEY(PRODUCT_ID)
)
GO
CREATE TABLE PRODUCT_ROW
(
PRODUCT_ROW_ID INT IDENTITY(1,1) NOT NULL,
PRODUCT_ID INT NOT NULL,
PRODUCT_DESC VARCHAR(50),
Quarter_ID TINYINT DEFAULT 0 NOT NULL,
Quarter_Amt NUMERIC(13,2) DEFAULT 0 NOT NULL,
CONSTRAINT PK_PRODUCT_ROW PRIMARY KEY(PRODUCT_ROW_ID)
)
GO
– Insert into PRODUCT_COL
INSERT INTO PRODUCT_COL(PRODUCT_DESC,Q1_SALES_AMT,Q2_SALES_AMT,Q3_SALES_AMT,Q4_SALES_AMT)
VALUES(’Sharpie’,130,215,190,300)
GO
INSERT INTO PRODUCT_COL(PRODUCT_DESC,Q1_SALES_AMT,Q2_SALES_AMT,Q3_SALES_AMT,Q4_SALES_AMT)
VALUES(’Pencils’,2100,918,3280,1315)
GO
– Insert into PRODUCT_ROW
INSERT INTO PRODUCT_ROW(PRODUCT_ID,PRODUCT_DESC,Quarter_ID, Quarter_Amt)
VALUES(1, ‘Sharpie’,1, 130)
GO
INSERT INTO PRODUCT_ROW(PRODUCT_ID,PRODUCT_DESC,Quarter_ID, Quarter_Amt)
VALUES(1, ‘Sharpie’,2, 215)
GO
INSERT INTO PRODUCT_ROW(PRODUCT_ID,PRODUCT_DESC,Quarter_ID, Quarter_Amt)
VALUES(1, ‘Sharpie’,3, 190)
GO
INSERT INTO PRODUCT_ROW(PRODUCT_ID,PRODUCT_DESC,Quarter_ID, Quarter_Amt)
VALUES(1, ‘Sharpie’,4, 300)
GO
INSERT INTO PRODUCT_ROW(PRODUCT_ID,PRODUCT_DESC,Quarter_ID,Quarter_Amt)
VALUES(2,’Pencils’,1,2100)
GO
INSERT INTO PRODUCT_ROW(PRODUCT_ID,PRODUCT_DESC,Quarter_ID,Quarter_Amt)
VALUES(2,’Pencils’,2,91
GO
INSERT INTO PRODUCT_ROW(PRODUCT_ID,PRODUCT_DESC,Quarter_ID,Quarter_Amt)
VALUES(2,’Pencils’,3,3280)
GO
INSERT INTO PRODUCT_ROW(PRODUCT_ID,PRODUCT_DESC,Quarter_ID,Quarter_Amt)
VALUES(2,’Pencils’,4,1315)
GO
Now we will work with queries. Queries we are going to show will work on any databases. We will start with PRODUCT_COL table where quarterly sale is stored in individual column for a given product (in a single row) and we want to show sale for each quarter on individual row. Following query will do that.
SELECT PRODUCT_ID, PRODUCT_DESC, 1 AS Quarter, Q1_SALES_AMT as Quarterly_Sales
FROM PRODUCT_COL
UNION ALL
SELECT PRODUCT_ID, PRODUCT_DESC, 2 AS Quarter, Q2_SALES_AMT as Quarterly_Sales
FROM PRODUCT_COL
UNION ALL
SELECT PRODUCT_ID, PRODUCT_DESC, 3 AS Quarter, Q3_SALES_AMT as Quarterly_Sales
FROM PRODUCT_COL
UNION ALL
SELECT PRODUCT_ID, PRODUCT_DESC, 4 AS Quarter, Q4_SALES_AMT as Quarterly_Sales
FROM PRODUCT_COL
ORDER BY Product_ID
GO
Here is the result.
PRODUCT_ID PRODUCT_DESC Quarter Quarterly_Sales
———– ————— ———– —————–
1 Sharpie 1 130.00
1 Sharpie 2 215.00
1 Sharpie 3 190.00
1 Sharpie 4 300.00
2 Pencils 1 2100.00
2 Pencils 2 918.00
2 Pencils 3 3280.00
2 Pencils 4 1315.00
In above query, we transposed columns to rows using simple UNION ALL operator Now let us see how we can transform rows into columns. This gets bit trickier than the previous one.
SELECT Product_ID, Product_Desc,
MAX(CASE WHEN Quarter_ID = 1 THEN Quarter_Amt ELSE 0 END) AS Q1_Sales,
MAX(CASE WHEN Quarter_ID = 2 THEN Quarter_Amt ELSE 0 END) AS Q2_Sales,
MAX(CASE WHEN Quarter_ID = 3 THEN Quarter_Amt ELSE 0 END) AS Q3_Sales,
MAX(CASE WHEN Quarter_ID = 4 THEN Quarter_Amt ELSE 0 END) AS Q4_Sales
FROM PRODUCT_ROW
GROUP BY Product_ID, Product_Desc
ORDER BY Product_ID
GO
Results are shown as under.
Product_ID Product_Desc Q1_Sales Q2_Sales Q3_Sales Q4_Sales
———– ————- ——— ——– ——– ———
1 Sharpie 130.00 215.00 190.00 300.00
2 Pencils 2100.00 918.00 3280.00 1315.00
In above query, since we need only one row for each product, we are doing group by on the product_id and product_desc column. To facilitate grouping we have to use aggregate function and hence MAX is used. You can use other aggregation function like SUM or MIN as well in this scenario. But be careful with usage of aggregate function as it may give you wrong result based on your data. Last is the CASE statement which provides the condition for the column. For first column we want to pick up only quarter_id = 1 record. If quarter_id is not 1 then we would like to ignore the value. Similarly we are performing case condition for rest of the quarters. Same result can be achieved using DECODE statement in Oracle but it will become specific to Oracle only.