프로그래밍/MSSQL

mssql 세로 데이터를 동적으로 가로로 출력 with asp

p-a-r-k 2021. 3. 8. 16:03
반응형

집계조회 페이지를 만들어야 하는데, 상품별 데이터는 세로로 나와야하고,

특정 데이터(여기서는 창고)는 가로로 동적으로 만들어야 해서 정리합니다..

 

주요 테이블은 2개입니다. 현재 서비스에는 상품을 관리할 수 있는 상품테이블이 있고,

여러가지 기능 중에 창고간에 상품을 이동시킬 수 있는 창고이동이란 기능이 있습니다.

1. 상품테이블

2. 창고이동 테이블

 

구현해야 할 화면은 아래와 같습니다.

엑셀 화면정의

일단 기준은 창고이동 테이블의 데이터이므로 "계" 셀 뒤의 데이터를 조회해봅니다..

SELECT 
A.PRODUCT_CODE, SUM(A.TOTAL_AMOUNT) AS TOTAL_AMOUNT
FROM [창고이동테이블] A
WHERE [조건]
GROUP BY A.PRODUCT_CODE

DB 조회 결과

상품 코드별로 그룹지어 이동수량을 조회했습니다.. 이걸 가로로 출력하면되네요..

창고 컬럼을 구하기위해 커서를 사용하여 문자열로 받아봅니다..

DECLARE @H_CODE2 NVARCHAR(6); -- 가로 창고 컬럼
DECLARE @H_CODE_GRP NVARCHAR(300) = ''; -- 전체 가로 창고 컬럼
DECLARE MYCUR CURSOR FOR -- 커서 선언

-- A.HOUSE_CODE2 => 가로에 나올 창고 코드
SELECT A.HOUSE_CODE2
FROM (
	SELECT A.HOUSE_CODE2
	FROM [창고이동 테이블] A
	WHERE [조건]
	AND RAP_DAY BETWEEN @sSDate AND @sEDate
	GROUP BY A.HOUSE_CODE2
)A ORDER BY HOUSE_CODE2 

OPEN MYCUR
FETCH NEXT FROM MYCUR INTO @H_CODE2

IF @H_CODE2 IS NULL
BEGIN
	SELECT NULL;
	CLOSE MYCUR
END
ELSE
BEGIN
	WHILE(@@FETCH_STATUS=0)
	BEGIN
		SET @H_CODE_GRP = @H_CODE_GRP + '['+@H_CODE2+'],' -- 컬럼 누적
		FETCH NEXT FROM MYCUR INTO @H_CODE2
	END
	CLOSE MYCUR
	DEALLOCATE MYCUR

	SET @H_CODE_GRP = LEFT(@H_CODE_GRP, LEN(@H_CODE_GRP)-1); -- 맨 뒤 콤마 제거

	SELECT @H_CODE_GRP;
END

DB 조회 결과

현재 [창고이동 테이블]에는 총 2개의 창고에 이동한 데이터가 있기때문에 위처럼 2가지의 코드가 조회됐네요..

그럼 저 코드로 기존에 조회한 창고이동 데이터와 연결시켜 가로로 출력합니다..

그룹핑할 컬럼을 명시하고, 수량은 SUM으로 추출한뒤 PIVOT으로 가로로 출력할 데이터를 매칭하면 됩니다..

엑셀 화면정의에 앞쪽컬럼인 유형, 상품명, 규격, 입수수량등은 JOIN이나 FUNCTION으로 가져올 수가 있겠죠..

DECLARE @sHCodeCols VARCHAR(300) = '위에서 커서로 합친 문자열 ex) [0002], [0003]';

EXEC('
	SELECT 
	dbo.FUN_PRODUCT_TYPE_NAME(B.AGENT_NO, A.PRODUCT_CODE, B.TYPE_CODE1) AS ''유형'', 
	B.PRODUCT_NAME AS ''상품명'', 
	B.STANDARD AS ''규격'', 
	B.BOX_GET_AMOUNT AS ''입수수량'',
	A.*
	FROM (
		SELECT * FROM(
			SELECT
			PRODUCT_CODE,
			HOUSE_CODE2,
			SUM(TOTAL_AMOUNT) AS TOTAL_AMOUNT
			FROM [창고이동 테이블]
			WHERE [조건]
			GROUP BY PRODUCT_CODE, HOUSE_CODE2
		) Q
		PIVOT (
		SUM(TOTAL_AMOUNT) FOR HOUSE_CODE2 IN ('+ @sHCodeCols +')
		) AS P
	) A LEFT JOIN [상품 테이블] B
	ON A.PRODUCT_CODE = B.PRODUCT_CODE
	WHERE [조건] 
')

피벗테이블안에는 동적으로 파라미터를 받을 수 없기때문에,

피벗테이블 쿼리를 문자열로 만들어 그 문자열을 EXEC시켜주는 방식으로 조회해야합니다..

EXEC 쿼리 결과..

얼추 엑셀 화면정의와 비슷해졌네요.. 다만 UI에서는 0002, 0003부분을 실제 창고이름으로 보여줘야겠군요..

 

현재 서비스는 jquery기반의 classic asp로 돌아가고있습니다..

결과를 나타낼 테이블은 grid 플러그인인 w2ui를 사용해봅니다..

 

asp 상단에서는 프로시저를 3개로 나눴습니다.

컬럼 코드를 먼저돌린 후에 집계해주는 테이블에 코드문자열을 넘깁니다.. "[0002, 0003]"

1. 동적 창고 컬럼 코드

2. 동적 창고 컬럼 명 (UI 사용)

3. 실제 집계데이터 조회

 

하단 javascript 부분에서는 w2ui를 사용하여 헤더와 데이터를 매칭해줍니다..

$('#grid').w2grid({
    name: 'grid',
    header: '창고이동 집계표',
    show: {
        lineNumbers : true,
        footer: true
    },
    multiSearch: false,
    columns: [
        { field: 'f01', caption: '유형', size: '10%'},
        { field: 'f02', caption: '상품', size: '20%'},
        { field: 'f03', caption: '규격', size: '15%'},
        { field: 'f04', caption: '입수수량', size: '10%', render: 'number'},
        { field: 'f05', caption: '계', size: '10%', render: 'number'},
        <% IF nColsRow = -1 THEN %>
        <% ELSE %>
            <% For k = 0 To nColsRow %>
            { field: 'f0<%= k+6 %>', caption: '<%= ColsList(1, k) %>', size: '10%', render: 'number'},
            <% Next %>
        <% END IF %>
    ],
    multiSort : true,
    records: [
    <%
        IF sInitDiv = "1" AND nTotalRow > -1 THEN
            sRowIdx = ""

            For i = 0 to nTotalRow
                nSumAmount = 0 '계 구하기
        %>
            <% If i > 0 Then %>,<% End If %>
            {
                recid: "<%=i%>",
                f01: "<%=List(0,i)%>",
                f02: "<%=List(1,i)%>",
                f03: "<%=List(2,i)%>",
                f04: "<%=List(3,i)%>",
                <% IF nColsRow = -1 THEN %>
                <% ELSE %>
                    <% 
                    For k = 0 To nColsRow 
                        nSumAmount = nSumAmount + Cdbl(List(5 + k, i))
                    %>
                    "f0<%= k+6 %>": <%= List(5 + k, i) %>,
                    <% Next %>
                <% END IF %>
                f05: "<%=nSumAmount%>",
            }
        <%
            sRowIdx = List(0,i)
        Next
    Else
    %>
    {
        recid: "S-1",
        f01: "결과없음"
    }
    <% END IF

        IF nTotalRow > -1 THEN
    %>
        ,{
            summary: true,
            recid: "S-2",
            f01: "합 계",
            f03: "<%=nSumResult1%>",
            f04: "<%=nSumResult2%>",
            f05: "<%=nSumResult3%>",
            <% IF nColsRow = -1 THEN %>
            <% ELSE %>
                <% For k = 0 To nColsRow %>
                "f0<%= k+6 %>": 0,
                <% Next %>
            <% END IF %>
        }
    <% END IF %>
    ]
});

쿼리에서 수량 합"계" 는 구하지 않았기 때문에 실제 조회 결과로 스크립트쪽에서 합하여 출력해주었습니다..

동적 창고 컬럼은 For문으로 돌려준 것을 확인할 수 있습니다..

 

최종 결과 화면은 아래와 같아지네요... 소계와 합계를 구하는 일만 남았읍니다..

최종 결과

반응형