첨부 실행 코드는 나눔고딕코딩 폰트를 사용합니다.
728x90
반응형
728x170

TestProject.zip
다운로드

▶ MainForm.cs

using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Windows.Forms;

namespace TestProject
{
    /// <summary>
    /// 메인 폼
    /// </summary>
    public partial class MainForm : Form
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Field
        ////////////////////////////////////////////////////////////////////////////////////////// Private

        #region CREATE_SOURCE_TABLE_SQL

        /// <summary>
        /// CREATE_SOURCE_TABLE_SQL
        /// </summary>
        private const string CREATE_SOURCE_TABLE_SQL = @"
CREATE TABLE SOURCE_TABLE
(
    MANAGER      TEXT    NOT NULL
   ,SALES_MONTH  TEXT    NOT NULL
   ,SALES_AMOUNT INTEGER NOT NULL
   ,PRIMARY KEY(MANAGER, SALES_MONTH)
);
";

        #endregion
        #region INSERT_SOURCE_TABLE_SQL

        /// <summary>
        /// INSERT_SOURCE_TABLE_SQL
        /// </summary>
        private const string INSERT_SOURCE_TABLE_SQL = @"
INSERT INTO SOURCE_TABLE
(
    MANAGER
   ,SALES_MONTH
   ,SALES_AMOUNT
)
VALUES
(
    @MANAGER
   ,@SALES_MONTH
   ,@SALES_AMOUNT
);
";

        #endregion
        #region SELECT_COLUMN_COLUMN_VALUE_SQL

        /// <summary>
        /// SELECT_COLUMN_COLUMN_VALUE_SQL
        /// </summary>
        private const string SELECT_COLUMN_COLUMN_VALUE_SQL = "SELECT DISTINCT SALES_MONTH FROM SOURCE_TABLE ORDER BY SALES_MONTH ASC";

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Constructor
        ////////////////////////////////////////////////////////////////////////////////////////// Public

        #region 생성자 - MainForm()

        /// <summary>
        /// 생성자
        /// </summary>
        public MainForm()
        {
            InitializeComponent();

            this.Load += Form_Load;
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Private
        //////////////////////////////////////////////////////////////////////////////// Event

        #region 폼 로드시 처리하기 - Form_Load(sender, e)

        /// <summary>
        /// 폼 로드시 처리하기
        /// </summary>
        /// <param name="sender">이벤트 발생자</param>
        /// <param name="e">이벤트 인자</param>
        private void Form_Load(object sender, EventArgs e)
        {
            using(SQLiteConnection connection = SQLiteHelper.GetConnection("Data Source=:memory:"))
            {
                connection.Open();

                // 소스 테이블을 생성한다.
                SQLiteHelper.Execute(connection, CREATE_SOURCE_TABLE_SQL);

                // 소스 테이블에 데이터를 추가한다.
                InsertSourceTableData(connection);
                
                // 행 컬럼명 리스트를 설정한다.
                List<string> rowColumnNameList = SQLiteHelper.GetStringList("MANAGER");

                // 컬럼 컬럼 값 리스트를 설정한다.
                DataTable columnColumnValueTable = SQLiteHelper.GetDataTable(connection, SELECT_COLUMN_COLUMN_VALUE_SQL);

                List<string> columnColumnValueList = SQLiteHelper.GetStringList(columnColumnValueTable, "SALES_MONTH");

                // 정렬 컬럼명 리스트를 설정한다.
                List<string> orderByColumnNameList = SQLiteHelper.GetStringList("MANAGER");

                // 피벗 합계 테이블을 생성한다.
                string pivotSummarySQL = SQLiteHelper.GetPivotSummarySQL("SOURCE_TABLE", rowColumnNameList, "SALES_MONTH", columnColumnValueList,
                    "SALES_AMOUNT", orderByColumnNameList);

                SQLiteHelper.Execute(connection, "CREATE TABLE PIVOT_TABLE AS " + pivotSummarySQL);

                // 소스 테이블을 출력한다.
                DataTable sourceTable = SQLiteHelper.GetDataTable(connection, "SELECT * FROM SOURCE_TABLE");

                // 소스 데이터 그리드 뷰에 소스 테이블을 바인딩 한다.
                this.sourceDataGridView.DataSource = sourceTable;

                // 피벗 테이블을 출력한다.
                DataTable pivotTable = SQLiteHelper.GetDataTable(connection, "SELECT * FROM PIVOT_TABLE");

                // 피벗 데이터 그리드 뷰에 피벗 테이블을 바인딩 한다.
                this.pivotDataGridView.DataSource = pivotTable;
            }
        }

        #endregion

        //////////////////////////////////////////////////////////////////////////////// Function

        #region 소스 데이터 추가하기 - InsertSourceData(connection, lotWafer, tkinTime, stepSeq, subItemID, value)

        /// <summary>
        /// 소스 데이터 추가하기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <param name="lotWafer">LOT_WF</param>
        /// <param name="tkinTime">TKIN_TIME</param>
        /// <param name="stepSeq">STEP_SEQ</param>
        /// <param name="subItemID">SUB_ITEM_ID</param>
        /// <param name="value">VALUE</param>
        /// <returns>처리 행 수</returns>
        private int InsertSourceData(SQLiteConnection connection, string manager, string salesMonth, double salesAmount)
        {
            List<SQLiteParameter> parameterList = new List<SQLiteParameter>()
            {
                new SQLiteParameter { ParameterName = "@MANAGER"     , DbType = DbType.String, Value = manager    },
                new SQLiteParameter { ParameterName = "@SALES_MONTH" , DbType = DbType.String, Value = salesMonth },
                new SQLiteParameter { ParameterName = "@SALES_AMOUNT", DbType = DbType.Double, Value = salesAmount}
            };

            int result = SQLiteHelper.Execute(connection, INSERT_SOURCE_TABLE_SQL, parameterList);

            return result;
        }

        #endregion
        #region 소스 테이블 데이터 추가하기 - InsertSourceTableData(connection)

        /// <summary>
        /// 소스 테이블 데이터 추가하기
        /// </summary>
        /// <param name="connection">연결</param>
        private void InsertSourceTableData(SQLiteConnection connection)
        {
            DateTime currentTime = DateTime.Now;

            InsertSourceData(connection, "홍길동", "01", 1000000);
            InsertSourceData(connection, "홍길동", "02", 1500000);
            InsertSourceData(connection, "홍길동", "03", 1000000);
            InsertSourceData(connection, "홍길동", "04", 2000000);
            InsertSourceData(connection, "홍길동", "05", 3000000);
            InsertSourceData(connection, "홍길동", "06", 1500000);
            InsertSourceData(connection, "홍길동", "07", 1000000);
            InsertSourceData(connection, "홍길동", "08", 1000000);
            InsertSourceData(connection, "홍길동", "09", 3000000);
            InsertSourceData(connection, "홍길동", "10", 4000000);
            InsertSourceData(connection, "홍길동", "11", 1000000);
            InsertSourceData(connection, "홍길동", "12", 1500000);

            InsertSourceData(connection, "김철수", "01",  500000);
            InsertSourceData(connection, "김철수", "02", 1500000);
            InsertSourceData(connection, "김철수", "03", 2000000);
            InsertSourceData(connection, "김철수", "04", 2500000);
            InsertSourceData(connection, "김철수", "05", 1000000);
            InsertSourceData(connection, "김철수", "06", 2500000);
            InsertSourceData(connection, "김철수", "07", 1500000);
            InsertSourceData(connection, "김철수", "08",  500000);
            InsertSourceData(connection, "김철수", "09",  500000);
            InsertSourceData(connection, "김철수", "10", 2000000);
            InsertSourceData(connection, "김철수", "11", 1000000);
            InsertSourceData(connection, "김철수", "12", 2500000);

            InsertSourceData(connection, "이영희", "01", 2500000);
            InsertSourceData(connection, "이영희", "02", 3500000);
            InsertSourceData(connection, "이영희", "03", 1000000);
            InsertSourceData(connection, "이영희", "04", 1500000);
            InsertSourceData(connection, "이영희", "05", 5000000);
            InsertSourceData(connection, "이영희", "06", 1500000);
            InsertSourceData(connection, "이영희", "07",  500000);
            InsertSourceData(connection, "이영희", "08",  500000);
            InsertSourceData(connection, "이영희", "09",  500000);
            InsertSourceData(connection, "이영희", "10",  500000);
            InsertSourceData(connection, "이영희", "11", 1000000);
            InsertSourceData(connection, "이영희", "12", 2000000);
        }

        #endregion
    }
}

 

728x90

 

▶ SQLiteHelper.cs

using System.Collections.Generic;
using System.Data;
using System.Data.SQLite;
using System.Text;

namespace TestProject
{
    /// <summary>
    /// SQLITE 헬퍼
    /// </summary>
    public static class SQLiteHelper
    {
        //////////////////////////////////////////////////////////////////////////////////////////////////// Method
        ////////////////////////////////////////////////////////////////////////////////////////// Static
        //////////////////////////////////////////////////////////////////////////////// Public

        #region 연결 구하기 - GetConnection(connectionString)

        /// <summary>
        /// 연결 구하기
        /// </summary>
        /// <param name="connectionString">연결 문자열</param>
        /// <returns>연결</returns>
        public static SQLiteConnection GetConnection(string connectionString)
        {
            SQLiteConnection connection = new SQLiteConnection(connectionString);

            return connection;
        }

        #endregion
        #region 명령 구하기 - GetCommand(connection)

        /// <summary>
        /// 명령 구하기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <returns>명령</returns>
        public static SQLiteCommand GetCommand(SQLiteConnection connection)
        {
            SQLiteCommand command = new SQLiteCommand(connection);

            return command;
        }

        #endregion
        #region 데이터 어댑터 구하기 - GetDataAdapter()

        /// <summary>
        /// 데이터 어댑터 구하기
        /// </summary>
        /// <returns>데이터 어댑터</returns>
        public static SQLiteDataAdapter GetDataAdapter()
        {
            SQLiteDataAdapter dataAdapter = new SQLiteDataAdapter();

            return dataAdapter;
        }

        #endregion

        #region 실행하기 - Execute(connection, commandText, parameterList)

        /// <summary>
        /// 실행하기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <param name="commandText">명령 텍스트</param>
        /// <param name="parameterList">매개 변수 리스트</param>
        /// <returns>처리 행 수</returns>
        public static int Execute(SQLiteConnection connection, string commandText, List<SQLiteParameter> parameterList = null)
        {
            using(SQLiteCommand command = GetCommand(connection))
            {
                command.CommandText = commandText;

                if(parameterList != null)
                {
                    foreach(SQLiteParameter parameter in parameterList)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                int result = command.ExecuteNonQuery();

                return result;
            }
        }

        #endregion
        #region 스칼라 실행하기 - ExecuteScalar(connection, commandText, parameterList)

        /// <summary>
        /// 스칼라 실행하기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <param name="commandText">명령 텍스트</param>
        /// <param name="parameterList">매개 변수 리스트</param>
        /// <returns>스칼라 값</returns>
        public static object ExecuteScalar(SQLiteConnection connection, string commandText, List<SQLiteParameter> parameterList = null)
        {
            using(SQLiteCommand command = GetCommand(connection))
            {
                command.CommandText = commandText;

                if(parameterList != null)
                {
                    foreach(SQLiteParameter parameter in parameterList)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                object result = command.ExecuteScalar();

                return result;
            }
        }

        #endregion
        #region 데이터 리더 실행하기 - ExecuteReader(connection, commandText, parameterList)

        /// <summary>
        /// 데이터 리더 실행하기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <param name="commandText">명령 텍스트</param>
        /// <param name="parameterList">매개 변수 리스트</param>
        /// <returns>데이터 리더</returns>
        public static SQLiteDataReader ExecuteDataReader(SQLiteConnection connection, string commandText, List<SQLiteParameter> parameterList = null)
        {
            using(SQLiteCommand command = GetCommand(connection))
            {
                command.CommandText = commandText;

                if(parameterList != null)
                {
                    foreach(SQLiteParameter parameter in parameterList)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                SQLiteDataReader dataReader = command.ExecuteReader();

                return dataReader;
            }
        }

        #endregion
        #region 데이터 테이블 구하기 - GetDataTable(connection, commandText, parameterList)

        /// <summary>
        /// 데이터 테이블 구하기
        /// </summary>
        /// <param name="connection">연결</param>
        /// <param name="commandText">명령 텍스트</param>
        /// <param name="parameterList">매개 변수 리스트</param>
        /// <returns>데이터 테이블</returns>
        public static DataTable GetDataTable(SQLiteConnection connection, string commandText, List<SQLiteParameter> parameterList = null)
        {
            using(SQLiteCommand command = GetCommand(connection))
            {
                command.CommandText = commandText;

                if(parameterList != null)
                {
                    foreach(SQLiteParameter parameter in parameterList)
                    {
                        command.Parameters.Add(parameter);
                    }
                }

                SQLiteDataAdapter dataAdapter = GetDataAdapter();

                dataAdapter.SelectCommand = command;

                DataTable dataTable = new DataTable();

                dataAdapter.Fill(dataTable);

                return dataTable;
            }
        }

        #endregion

        #region 문자열 리스트 구하기 - GetStringList(sourceArray)

        /// <summary>
        /// 문자열 리스트 구하기
        /// </summary>
        /// <param name="sourceArray">소스 배열</param>
        /// <returns>문자열 리스트</returns>
        public static List<string> GetStringList(params string[] sourceArray)
        {
            List<string> list = new List<string>();

            foreach(string source in sourceArray)
            {
                list.Add(source);
            }

            return list;
        }

        #endregion
        #region 문자열 리스트 구하기 - GetStringList(sourceTable, columnName)

        /// <summary>
        /// 리스트 구하기
        /// </summary>
        /// <param name="sourceTable">소스 테이블</param>
        /// <param name="columnName">컬럼명</param>
        /// <returns>문자열 리스트</returns>
        public static List<string> GetStringList(DataTable sourceTable, string columnName)
        {
            List<string> list = new List<string>();

            foreach(DataRow sourceRow in sourceTable.Rows)
            {
                list.Add(sourceRow[columnName].ToString());
            }

            return list;
        }

        #endregion

        #region 피벗 합계 SQL 구하기 - GetPivotSummarySQL(tableName, rowColumnNameList, columnColumnName, columnColumnValueList, valueColumnName, orderByColumnNameList)

        /// <summary>
        /// 피벗 합계 SQL 구하기
        /// </summary>
        /// <param name="tableName">테이블명</param>
        /// <param name="rowColumnNameList">행 컬럼명 리스트</param>
        /// <param name="columnColumnName">컬럼 컬럼명</param>
        /// <param name="columnColumnValueList">컬럼 컬럼 값 리스트</param>
        /// <param name="valueColumnName">값 컬럼명</param>
        /// <param name="orderByColumnNameList">정렬 컬럼명 리스트</param>
        /// <returns>피벗 SQL</returns>
        public static string GetPivotSummarySQL(string tableName, List<string> rowColumnNameList, string columnColumnName, List<string> columnColumnValueList,
            string valueColumnName, List<string> orderByColumnNameList)
        {
            string sql = @"
SELECT
    [ROW_COLUMN_NAME_LIST]
    [COLUMN_COLUMN_VALUE_LIST]
FROM [TABLE_NAME]
GROUP BY [ROW_COLUMN_NAME_LIST]
ORDER BY [ORDER_BY_COLUMN_NAME_LIST];
";

            #region 행 컬럼명 문자열 빌더를 설정한다.

            StringBuilder rowColumnNameStringBuilder = new StringBuilder();

            foreach(string column in rowColumnNameList)
            {
                rowColumnNameStringBuilder.AppendFormat("{0},", column);
            }

            #endregion
            #region 컬럼 컬럼 값 문자열 빌더를 설정한다.

            StringBuilder columnColumnValueStringBuilder = new StringBuilder();

            foreach(string columnColumnValue in columnColumnValueList)
            {
                columnColumnValueStringBuilder.AppendFormat
                (
                    ",SUM(CASE WHEN [{0}] = '{1}' THEN {2} ELSE 0 END) AS [{1}]",
                    columnColumnName,
                    columnColumnValue,
                    valueColumnName
                );

                columnColumnValueStringBuilder.AppendLine();
            }

            #endregion
            #region 정렬 컬럼명 문자열 빌더를 설정한다.

            StringBuilder orderByColumnNameStringBuilder = new StringBuilder();

            foreach(string orderByColumnName in orderByColumnNameList)
            {
                orderByColumnNameStringBuilder.AppendFormat("{0},", orderByColumnName);
            }

            #endregion

            sql = sql.Replace("[ROW_COLUMN_NAME_LIST]"     , rowColumnNameStringBuilder.ToString().TrimEnd(',')    );
            sql = sql.Replace("[COLUMN_COLUMN_VALUE_LIST]" , columnColumnValueStringBuilder.ToString()             );
            sql = sql.Replace("[TABLE_NAME]"               , tableName                                             );
            sql = sql.Replace("[ORDER_BY_COLUMN_NAME_LIST]", orderByColumnNameStringBuilder.ToString().TrimEnd(','));

            return sql;
        }

        #endregion
        #region 피벗 카운트 SQL 구하기 - GetPivotCountSQL(tableName, rowColumnNameList, columnColumnName, columnColumnValueList, orderByColumnNameList)

        /// <summary>
        /// 피벗 카운트 SQL 구하기
        /// </summary>
        /// <param name="tableName">테이블명</param>
        /// <param name="rowColumnNameList">행 컬럼명 리스트</param>
        /// <param name="columnColumnName">컬럼 컬럼명</param>
        /// <param name="columnColumnValueTable">컬럼 컬럼 값 리스트</param>
        /// <param name="valueColumnName">값 컬럼명</param>
        /// <param name="valueFunctionName">값 함수명</param>
        /// <param name="orderByColumnNameList">정렬 컬럼명 리스트</param>
        /// <returns>피벗 SQL</returns>
        public static string GetPivotCountSQL(string tableName, List<string> rowColumnNameList, string columnColumnName,
            List<string> columnColumnValueList, List<string> orderByColumnNameList)
        {
            string sql = @"
SELECT
    [ROW_COLUMN_NAME_LIST]
    [COLUMN_COLUMN_VALUE_LIST]
FROM [TABLE_NAME]
GROUP BY [ROW_COLUMN_NAME_LIST]
ORDER BY [ORDER_BY_COLUMN_NAME_LIST];
";

            #region 행 컬럼명 문자열 빌더를 설정한다.

            StringBuilder rowColumnNameStringBuilder = new StringBuilder();

            foreach(string rowColumnName in rowColumnNameList)
            {
                rowColumnNameStringBuilder.AppendFormat("{0},", rowColumnName);
            }

            #endregion
            #region 컬럼 컬럼 값 문자열 빌더를 설정한다.

            StringBuilder columnColumnValueStringBuilder = new StringBuilder();

            foreach(string columnColumnValue in columnColumnValueList)
            {
                columnColumnValueStringBuilder.AppendFormat(",SUM(CASE WHEN [{0}] = '{1}' THEN 1 ELSE 0 END) AS [{1}]", columnColumnName, columnColumnValue);

                columnColumnValueStringBuilder.AppendLine();
            }

            #endregion
            #region 정렬 컬럼명 문자열 빌더를 설정한다.

            StringBuilder orderByColumnNameStringBuilder = new StringBuilder();

            foreach(string orderByColumnName in orderByColumnNameList)
            {
                orderByColumnNameStringBuilder.AppendFormat("{0},", orderByColumnName);
            }

            #endregion

            sql = sql.Replace("[ROW_COLUMN_NAME_LIST]"     , rowColumnNameStringBuilder.ToString().TrimEnd(',')    );
            sql = sql.Replace("[COLUMN_COLUMN_VALUE_LIST]" , columnColumnValueStringBuilder.ToString()             );
            sql = sql.Replace("[TABLE_NAME]"               , tableName                                             );
            sql = sql.Replace("[ORDER_BY_COLUMN_NAME_LIST]", orderByColumnNameStringBuilder.ToString().TrimEnd(','));

            return sql;
        }

        #endregion
        #region 피벗 평균 SQL 구하기 - GetPivotAverageSQL(summaryTableName, countTableName, rowColumnNameList, columnColumnValueList, orderByColumnNameList)

        /// <summary>
        /// 피벗 평균 SQL 구하기
        /// </summary>
        /// <param name="summaryTableName">합계 테이블명</param>
        /// <param name="countTableName">카운트 테이블명</param>
        /// <param name="rowColumnNameList">행 컬럼명 리스트</param>
        /// <param name="columnColumnValueList">컬럼 컬럼 값 리스트</param>
        /// <param name="orderByColumnNameList">정렬 컬럼명 리스트</param>
        /// <returns>피벗 평균 SQL</returns>
        public static string GetPivotAverageSQL(string summaryTableName, string countTableName, List<string> rowColumnNameList,
            List<string> columnColumnValueList, List<string> orderByColumnNameList)
        {
            string sql = @"
SELECT
    [ROW_COLUMN_NAME_LIST]
    [COLUMN_COLUMN_VALUE_LIST]
FROM       [SUMMARY_TABLE_NAME] A
INNER JOIN [COUNT_TABLE_NAME] B ON [JOIN_CONDITION]
ORDER BY   [ORDER_BY_COLUMN_NAME_LIST];
";

            #region 행 컬럼명 문자열 빌더를 설정한다.

            StringBuilder rowColumnNameStringBuilder = new StringBuilder();

            foreach(string rowColumnName in rowColumnNameList)
            {
                rowColumnNameStringBuilder.AppendFormat("A.{0},", rowColumnName);
            }

            #endregion
            #region 컬럼 컬럼 값 문자열 빌더를 설정한다.

            StringBuilder columnColumnValueStringBuilder = new StringBuilder();

            foreach(string columnColumnValue in columnColumnValueList)
            {
                columnColumnValueStringBuilder.AppendFormat(",CASE WHEN B.[{0}] <> 0 THEN A.[{0}] / B.[{0}] ELSE 0 END AS [{0}]", columnColumnValue);

                columnColumnValueStringBuilder.AppendLine();
            }

            #endregion
            #region 조인 조건 문자열 빌더를 설정한다.

            StringBuilder joinConditionStringBuilder = new StringBuilder();

            foreach(string rowColumnName in rowColumnNameList)
            {
                if(joinConditionStringBuilder.Length > 0)
                {
                    joinConditionStringBuilder.Append(" AND ");
                }

                joinConditionStringBuilder.AppendFormat("B.{0} = A.{0}", rowColumnName);
            }

            #endregion
            #region 정렬 컬럼명 문자열 빌더를 설정한다.

            StringBuilder orderByColumnNameStringBuilder = new StringBuilder();

            foreach(string orderByColumnName in orderByColumnNameList)
            {
                orderByColumnNameStringBuilder.AppendFormat("A.{0},", orderByColumnName);
            }

            #endregion

            sql = sql.Replace("[ROW_COLUMN_NAME_LIST]"     , rowColumnNameStringBuilder.ToString().TrimEnd(',')    );
            sql = sql.Replace("[COLUMN_COLUMN_VALUE_LIST]" , columnColumnValueStringBuilder.ToString()             );
            sql = sql.Replace("[SUMMARY_TABLE_NAME]"       , summaryTableName                                      );
            sql = sql.Replace("[COUNT_TABLE_NAME]"         , countTableName                                        );
            sql = sql.Replace("[JOIN_CONDITION]"           , joinConditionStringBuilder.ToString()                 );
            sql = sql.Replace("[ORDER_BY_COLUMN_NAME_LIST]", orderByColumnNameStringBuilder.ToString().TrimEnd(','));
            
            return sql;
        }

        #endregion
    }
}
728x90
반응형
그리드형(광고전용)
Posted by icodebroker

댓글을 달아 주세요