Sometimes you got business requirements to create new view or dashboard and to do this you need to convert rows to columns. You can transpose them on two ways: using CASE or PIVOT function. In this post I will show you how to convert rows to columns at SQL Server on these two ways.
To explain you how to use pivot function, first we create PRODUCT table and insert some samples records in it.
CREATE TABLE MASTER.DBO.PRODUCT ( PRODUCER_NME VARCHAR(100), PRODUCT_NME VARCHAR(100), R_YR INT, R_MTH INT, SOLD_NO NUMERIC(7,2) );
And this is the data preview:
PIVOT function allows us quickly transpose rows into columns. Let’s look at the example below!
SELECT * FROM ( SELECT PRODUCER_NME, PRODUCT_NME, R_YR, R_MTH,SOLD_NO FROM MASTER.DBO.PRODUCT ) T PIVOT ( SUM(SOLD_NO) FOR R_MTH IN (, , ) ) PIV;
R_MTH in my table is the attribute which I want to see as columns, but as value for this attributes I want to see sum of the SOLD_NO values. I can write SELECT *, because my table not contain any additional attributes – in case when your table have a lot of columns, probably you will must limit them.
The screenshot below presents like my data look after using the PIVOT function.
You can get the same results as using the pivot function using the CASE statement. CASE statement is used more often than PIVOT so you can feel more comfortable using it. But remember what is the better, quicker and shorter depends how many attributes, calculation and conditions you need to use. And in most cases the PIVOT function wins 🙂
SELECT PRODUCER_NME, PRODUCT_NME, R_YR, SUM(CASE WHEN R_MTH = 1 THEN SOLD_NO ELSE NULL END) AS MTH_1, SUM(CASE WHEN R_MTH = 2 THEN SOLD_NO ELSE NULL END) AS MTH_2, SUM(CASE WHEN R_MTH = 3 THEN SOLD_NO ELSE NULL END) AS MTH_3 FROM MASTER.DBO.PRODUCT GROUP BY PRODUCER_NME, PRODUCT_NME, R_YR;
The SELECT statement above can give you the same result like the PIVOT function.
If you want to download all the scripts using in this post, you can download the Products.txt file below:
If you enjoyed this post please add the comment below or share this post on your Facebook, Twitter, LinkedIn or another social media webpage.
Thanks in advanced!