MSSQL PIVOT 동적 - MSSQL PIVOT dongjeog

skymong9.egloos.com/1836594

MS SQL 서버 2005에서는 PIVOT 함수와 UNPIVOT 함수를 제공하고 있습니다. 이 기능을 이용하면 피벗 테이블을 쉽게 작성 할 수 있습니다. 하지만 기본적으로 제공되는 기능만을 이용하면 동적인 피벗 테이블을 구현 할 수 없습니다. 이를 해결 할 수 있는 방법 한 가지를 같이 살펴보고자 합니다.

1. 테이블 생성

다음과 같은 테스트에 사용할 테이블을 만들도록 하겠습니다.

USE tempdb
GO

CREATE TABLE Orders (
   Customer varchar(8),
   Product varchar(5),
   Quantity int
)
GO

INSERT INTO Orders VALUES('Mike', 'Bike', 3)
INSERT INTO Orders VALUES('Mike', 'Chain', 2)
INSERT INTO Orders VALUES('Mike', 'Bike', 5)
INSERT INTO Orders VALUES('Lisa', 'Bike', 3)
INSERT INTO Orders VALUES('Lisa', 'Chain', 3)
INSERT INTO Orders VALUES('LIsa', 'Bike', 4)
GO

SELECT * FROM Orders
GO

마지막 SELECT 문의 결과는 다음과 같습니다.

Customer Product Quantity-------- ------- -----------Mike     Bike    3Mike     Chain   2Mike     Bike    5Lisa     Bike    3Lisa     Chain   3LIsa     Bike    4(6개 행 적용됨)

2. PIVOT 함수 이용

다음과 같이 PIVOT 함수릉 이용해 우선은 간단하나마 피벗 테이블을 만들어 낼 수 있습니다.

SELECT * FROM Orders
   PIVOT (SUM(Quantity) FOR Product IN (Bike, Chain)
) AS PVT
GO

결과는 다음과 같습니다.

Customer Bike        Chain-------- ----------- -----------Lisa     7           3Mike     8           2(2개 행 적용됨)

(혹 PIVOT 함수에 대해 모르시는 분들은 [SQL배워보기]-[2005강좌]의 내용을 참고하시기 바랍니다)

여기서 고민하고자 하는 부분은 위 SELECT 문에서의 FOR Product IN (Bike, Chain) 부분입니다. 이 부분이 피벗 테이블 결과에서 가로로 나열될 컬럼을 지정해주게 되는게 Bike, Chain 처럼 고정된 값을 지정해 주어야 한다는 것입니다. 하지만 실제 업무에 있어서는 가로로 나열될 컬럼이 고정적이지 않을 수 있기 때문에 사용에 한계를 느낄 수 있게 됩니다. 예를 들어 다음과 같이 Orders 테이블에 행을 추가해 보도록 하겠습니다.

INSERT INTO Orders VALUES('Mike', 'Chain', 1)
INSERT INTO Orders VALUES('Mike', 'Light', 2)
INSERT INTO Orders VALUES('Lisa', 'Chain', 2)
INSERT INTO Orders VALUES('LIsa', 'Light', 3)
GO

위 INSERT 문으로 인해 기존의 Product 컬럼에는 없었던 Light 가 기록되게 됩니다. 하지만 다음과 같이 기존의 PIVOT문을 이용해 결과를 확인해 보면

SELECT * FROM Orders
   PIVOT (SUM(Quantity) FOR Product IN (Bike, Chain)
) AS PVT
GO

결과는 다음과 같습니다.

Customer Bike        Chain-------- ----------- -----------Lisa     7           5Mike     8           3(2개 행 적용됨)

이 결과를 보면 추가로 입력된 Light 는 결과에 표시되지 않고 있습니다. 그럼 이 문제를 어떻게 해결 할까요?

3. Dynamic PIVOT 구현

여러가지 해결 방법이 있을 것이지만, 다음의 방법이 이 중 하나가 될 수 있습니다.

DECLARE @Prod varchar(2000)
SET @Prod = ''
SELECT @Prod = @Prod + '[' + Product + '],'
   FROM (SELECT Distinct Product FROM Orders) A

SET @Prod = LEFT(@Prod, LEN(@Prod) - 1)
EXEC ('SELECT * FROM Orders
        PIVOT ( SUM(Quantity) FOR Product IN (' + @Prod + ')
        ) AS PVT')
GO

우선 Orders 테이블에서 모든 Product를 DISTINCT를 이용해 한번씩만 가져와 콤마(,)로 연결을 한 후 가장 마지막의 콤마만 제거한 문자열을 갖는 @Prod 변수를 IN 부분에 연결하여 수행하고자 하는 SELECT 문을 만들어 EXEC()를 이용해 수행한 것입니다. [ ]로 Product의 값을 묶어 준 이유는 Product 값에 공백 등이 들어 있을 경우에 대비한 것입니다. 결과는 다음과 같습니다.

Customer Bike        Chain       Light-------- ----------- ----------- -----------Lisa     7           5           3Mike     8           3           2(2개 행 적용됨)

위 결과 처럼 새로운 Product가 입력된다 하더라고 결과에 그 내용이 포함되어 표시됩니다.

4. 정리

간단하나마 MS SQL 서버 2005의 PIVOT 함수를 좀더 효율 적으로 이용하는 방법을 살펴 보았습니다. 분명 이보다 더 좋은 방법이 있을 수 있습니다. 유일한 방법이 아님을 기억해 주시기 바랍니다.

In this article, I am going to explain how we can create a dynamic pivot table in SQL Server. Pivot tables are a piece of summarized information that is generated from a large underlying dataset. It is generally used to report on specific dimensions from the vast datasets. Essentially, the user can convert rows into columns. This gives the users the ability to transpose columns from a SQL Server table easily and create reports as per the requirements.

Some pivot tables are also created to help in data analysis, mainly for slicing and dicing with the data and generate analytical queries after all. If you see the figure below, you’ll have some idea how a pivot table is created from a table.

Figure 1 – Pivot Table Example

If you see the figure above, you can see that there are two tables. The table on the left is the actual table that contains the original records. The table on the right is a pivot table that is generated by converting the rows from the original table into columns. Basically, a pivot table will contain three specific areas, mainly – rows, columns, and values. In the above illustration, the rows are taken from the Student column, the columns are taken from the Subject, and the values are created by aggregating the Marks column.

Creating a sample data

Now that we have some idea about how a pivot table works let us go ahead and try our hands-on. You can execute the script below to create sample data, and we will try to implement the above illustration here.

Let us try to select the data from the table that we just created as below.

Figure 2 – Sample Dataset for Pivot Table

Applying the PIVOT Operator

Now that we have our data ready, we can go ahead and create the pivot table in SQL Server. Considering the same illustration as above, we will keep the Student column as rows and take the Subject for the columns. Also, another important point to note here is that while writing the query for the pivot table in SQL Server, we need to provide a distinct list of column values that we would like to visualize in the pivot table. For this script, we can see that we have three distinct subjects available in the original dataset, so we must provide these three in the list while creating the pivot table.

Figure 3 – Applying the PIVOT Operator

As you can see in the figure above, the pivot table has been created and we have converted the rows for Subjects into distinct columns.

Now let us try to break the above script and understand how it works. If you see the script, clearly, we can divide it into two separate sections – the first part in which we select data from the original table as it is and in the second part, we define how the pivot table should be created. In the script, we also mention some specific keywords like SUM, FOR and IN, which are meant for use by the PIVOT operator only. Let’s quickly talk about these keywords.

The SUM operator

In the script, I have used the SUM operator, which will essentially aggregate the values from the Marks column so that it can be used in the pivot table. It is mandatory for the pivot operator to use an aggregated column that it can display for the values sections.

The FOR keyword

The FOR keyword is a special keyword used for the pivot table in SQL Server scripts. This operator tells the pivot operator on which column do we need to apply the pivot function. Basically, the column which is to be converted from rows into columns.

The IN keyword

The IN keyword, as already explained above, lists all the distinct values from the pivot column that we want to add to the pivot table column list. For this example, since we have only three distinct values for the Subject column, we provide all the three in the list for the IN keyword.

The only limitation in this process is that we need to provide hardcoded values for the columns that we need to select from the pivot table. For instance, if a new subject value is inserted into the table, the pivot table won’t be able to display the new value as a column because it is not defined in the list for the IN operator. Let us go ahead and insert a few records into the table for a different subject – “History“.

Let us execute the query for displaying the pivot table as we did previously.

Figure 4 – Running the PIVOT Query

As you can see, the new subject that we just inserted into the table is not available in the PIVOT table. This is because we did not mention the new column in the IN list of the PIVOT operator. This is one of the limitations of the PIVOT table in SQL. Each time we want to include a new column in the PIVOT, we would need to go and modify the underlying code.

Another scenario would be like if the requirements change and now, we need to pivot students instead of the subjects, even in such a case, we would need to modify the entire query. In order to avoid this, we can create something dynamic in which we can configure the columns on which we would need the PIVOT table. Let’s go ahead and understand how to make a dynamic stored procedure that will return a PIVOT table in SQL.

Building a Dynamic Stored Procedure for PIVOT Tables

Let’s encapsulate the entire PIVOT script in a stored procedure. This stored procedure will have the configurable options in which we should be able to customize our requirements just by altering some parameterized values. The script for the dynamic PIVOT table in SQL is below.

As you can see in the script above, I have two parameterized variables. The details of these two parameters are as follows.

• @ColumnToPivot – This parameter accepts the name of the column in the base table on which the pivot table is going to be applied. For the current scenario, it will be the “Subject” column because we would like to pivot the base table and display all the subjects in the columns
• @ListToPivot – This parameter accepts the list of values that we want to visualize as a column in the pivot table in SQL

Executing the Dynamic Stored Procedure

Now that our dynamic stored procedure is ready let us go ahead and execute it. Let us replicate the first scenario where we visualized all the three subjects – Mathematics, Science and Geography in the pivot table in SQL. Execute the script as below.

Figure 5 – Executing Dynamic Stored Procedure

As you can see, we have now provided the name of the column “Subject” as the first parameter and the list of pivot columns as the second column.

Suppose, now we would also like to include the marks for the column “History” in this pivot table, the only thing that you should do is to add the name of the column in the second parameter and execute the stored procedure.

Figure 6 – Executing Dynamic Stored Procedure Modified

As easy as that, you can add as many columns you’d like to add to the list and the pivot table will be displayed accordingly.

Let us now consider another scenario where you need to display the name of the students on the columns and the subjects on the rows—just a vice versa scenario of what we have been doing all along. The solution is also simple, as you might have expected. We will just modify the values in both the parameters such that the first parameter indicates the column “Student” and the second parameter will contain the list of students that you want along with the columns. The stored procedure is as follows.

Figure 7 – Dynamic Stored Procedure

As you can see in the image above, the pivot table in SQL is dynamically modified without having to modify the underlying code behind.

Conclusion

In this article, I have explained what a pivot table in SQL is and how to create one. I have also demonstrated a simple scenario in which you can implement pivot tables. Finally, I have also shown how to parameterize the pivot table script such that the values table structure can be easily altered without having to modify the underlying code.

• Author
• Recent Posts

Aveek is an experienced Data and Analytics Engineer, currently working in Dublin, Ireland. His main areas of technical interest include SQL Server, SSIS/ETL, SSAS, Python, Big Data tools like Apache Spark, Kafka, and cloud technologies such as AWS/Amazon and Azure.

He is a prolific author, with over 100 articles published on various technical blogs, including his own blog, and a frequent contributor to different technical forums.

In his leisure time, he enjoys amateur photography mostly street imagery and still life. Some glimpses of his work can be found on Instagram. You can also find him on LinkedIn

View all posts by Aveek Das