Systems Engineering and RDBMS

Cross-tab reports: Changing data from row to column or column to row

Posted by decipherinfosys on August 27, 2007

We had covered this topic in the past using the PIVOT and UNPIVOT operators in SQL Server 20005. You can access that whitepaper on our web-site here. We have received some e-mails from our readers asking how to do this in prior versions of SQL Server or how to do this in other RDBMS. We will cover that in this blog post.

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,918 )
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. To know what things you need to keep in mind when you are using UNION/ UNION ALL, please refer to our previous blog post. 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.

2 Responses to “Cross-tab reports: Changing data from row to column or column to row”

  1. […] pivoting and un-pivoting of the data – you can access those at these links (link1, link2 and link3). In this post, we will present yet another easy way to do pivoting using the FOR XML PATH syntax […]

  2. […] Posted by decipherinfosys on December 3, 2007 Microsoft introduced the PIVOT and UNPIVOT operator in SQL Server 2005 to generate cross tab reports i.e. to convert rows into columns and columns into rows. You can read more on that in our whitepaper. We have also covered the traditional way of pivoting the data for prior version of SQL Server and for other databases in one of our previous blog post. […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: