[원문] http://www.sqlservercentral.com/articles/.Net/94922/
해외 사이트에서 다이나믹 피벗을 CLR 로 만든걸 보고 한번 테스트 해보았습니다. 피벗을 좀더 쉽게 사용 할수 있습니다.
성능은 장담 못하지만 ^^
소스 파일
DynamicPivot.cs컴파일 한 DLL 파일
DynamicPivot.dll
DynamicPivot.cs
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
namespace Syx.Functions
{
public partial class UserDefindedFunctions
{
[Microsoft.SqlServer.Server.SqlProcedure(Name="clrDynamicPivot")]
public static void clrDynamicPivot(SqlString query, SqlString pivotColumn, SqlString selectCols, SqlString aggCols, SqlString orderBy)
{
string stmt = string.Empty;
try
{
CreateTempTable(query);
string pivot = GetPivotData(pivotColumn.ToString());
stmt = string.Format("select * from ( select {0} from #temp ) as t pivot ( {1} for {2} in ( {3} )) as p {4}",
selectCols.ToString(),
aggCols.ToString(),
pivotColumn.ToString(),
pivot,
orderBy.ToString());
using (SqlConnection cn = new SqlConnection("Context Connection=True"))
{
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = stmt;
cn.Open();
SqlDataReader reader = cmd.ExecuteReader();
SqlContext.Pipe.Send(reader);
}
}
catch (Exception ex)
{
throw new Exception(string.Format("clrDynamicPivot Error stmt:{0}", stmt), ex);
}
}
public static void CreateTempTable(SqlString query)
{
using (SqlConnection sqlconn = new SqlConnection("Context Connection=True"))
{
SqlCommand sqlCmd = sqlconn.CreateCommand();
sqlCmd.CommandText = query.ToString();
sqlconn.Open();
sqlCmd.ExecuteNonQuery();
}
}
public static string GetPivotData(string pivotColumn)
{
string stmt = string.Format("select distinct {0} from #temp", pivotColumn);
string pivotCols = string.Empty;
using (SqlConnection cn = new SqlConnection("Context Connection=True"))
{
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = stmt;
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (dr.GetFieldType(0) == typeof(System.Int32))
pivotCols += "[" + dr.GetInt32(0) + "],";
if (dr.GetFieldType(0) == typeof(System.Decimal))
pivotCols += "[" + dr.GetDecimal(0) + "],";
if (dr.GetFieldType(0) == typeof(System.String))
pivotCols += "[" + dr.GetString(0) + "],";
}
}
}
return pivotCols.Remove(pivotCols.Length - 1);
}
}
}
DynamicPivot.cs C# 소스를 컴파일하여 DynamicPivot.dll 로 만듭니다.
생성된 DynamicPivot.dll SSMS 에서 실행 합니다.
1. CLR 활성화 및 ASSEMBLY 등록 합니다.
SET NOCOUNT ON;
USE master;
EXEC sp_configure 'clr enabled', 1;
RECONFIGURE;
GO
USE TEST
GO
CREATE ASSEMBLY DynamicPivot FROM 'D:\DynamicPivot.dll'
WITH PERMISSION_SET = SAFE
go
2. 프로시저를 만듭니다.
CREATE PROC usp_clrDynamicPivot
@query AS NVARCHAR(4000) ,
@pivotColumn AS NVARCHAR(4000) ,
@selectCols AS NVARCHAR(4000) ,
@aggCols AS NVARCHAR(4000) ,
@orderBy AS NVARCHAR(4000)
AS EXTERNAL NAME
DynamicPivot.[Syx.Functions.UserDefindedFunctions].clrDynamicPivot
3. 테스트 데이터 생성 합니다.
CREATE TABLE [dbo].[Orders]
(
[OrderID] [int] NOT NULL ,
[ProductID] [int] NOT NULL ,
[Quantity] [int] NOT NULL ,
[OriginState] [nvarchar](2) NOT NULL
)
GO
4. 데이터 입력 TEST #첫번째
INSERT INTO Orders VALUES (1, 10, 2, 'AA')
INSERT INTO Orders VALUES (2, 11, 1, 'BB')
INSERT INTO Orders VALUES (3, 12, 5, 'CC')
INSERT INTO Orders VALUES (3, 13, 10, 'DD')
INSERT INTO Orders VALUES (4, 14, 4, 'EE')
실행
DECLARE @query NVARCHAR(4000)
DECLARE @pivotColumn NVARCHAR(4000)
DECLARE @selectCols NVARCHAR(4000)
DECLARE @aggCols NVARCHAR(4000)
DECLARE @orderBy NVARCHAR(4000)
SET @query = 'select OriginState,ProductID, Quantity into #temp from dbo.Orders'
SET @pivotColumn = 'OriginState'
SET @selectCols = 'OriginState, ProductID, Quantity'
SET @aggCols = 'sum(Quantity)'
SET @orderBy = ''
EXECUTE usp_clrDynamicPivot @query, @pivotColumn, @selectCols, @aggCols,
@orderBy
GO
결과값
5 데이터 입력 TEST #두번째
INSERT INTO Orders VALUES (2, 1, 1, 'BB')
INSERT INTO Orders VALUES (2, 2, 2, 'BB')
INSERT INTO Orders VALUES (2, 3, 3, 'BB')
INSERT INTO Orders VALUES (2, 4, 4, 'BB')
INSERT INTO Orders VALUES (2, 5, 5, 'BB')
INSERT INTO Orders VALUES (3, 1, 1, 'CC')
INSERT INTO Orders VALUES (3, 2, 2, 'CC')
INSERT INTO Orders VALUES (3, 3, 3, 'CC')
INSERT INTO Orders VALUES (3, 4, 4, 'CC')
INSERT INTO Orders VALUES (3, 5, 5, 'CC')
INSERT INTO Orders VALUES (4, 1, 1, 'DD')
INSERT INTO Orders VALUES (4, 2, 2, 'DD')
INSERT INTO Orders VALUES (4, 3, 3, 'DD')
INSERT INTO Orders VALUES (4, 4, 4, 'DD')
INSERT INTO Orders VALUES (4, 5, 5, 'DD')
실행
DECLARE @query NVARCHAR(4000)
DECLARE @pivotColumn NVARCHAR(4000)
DECLARE @selectCols NVARCHAR(4000)
DECLARE @aggCols NVARCHAR(4000)
DECLARE @orderBy NVARCHAR(4000)
SET @query = 'select OriginState,ProductID, Quantity into #temp from dbo.Orders'
SET @pivotColumn = 'OriginState'
SET @selectCols = 'OriginState, ProductID, Quantity'
SET @aggCols = 'sum(Quantity)'
SET @orderBy = ''
EXECUTE usp_clrDynamicPivot @query, @pivotColumn, @selectCols, @aggCols,
@orderBy
GO
결과값
pivotColumn 컬럼으로 지정 한 'OriginState' 값에 따라 컬럼이 동적으로 피벗 된다.
