아래와 같은 쿼리문이 있습니다.

1일부터 31일까지 생산실적을 열이 아닌 행으로 조회하기 위해 case 구문을 사용하였습니다.

그러나 메모리 용량에 문제가 있어 테이블 변수를 선언해서 구문을 변경할려고 합니다.

pivot 테이블을 사용할려고 했지만 ms-sql 2005 호환성 문제로 인하여 ( 80 -> 90 변경시 다른 쿼리문을 사용못함 ) 제대로 되지 않네요.

무슨 좋은 방법 없을까요?

 

제가 원하는결과는 

품목코드 | 품목명 .....................| 합계 |     1일        |        2일     | 3일 | 4일 | 5일 | 6일 | 7일 | 8일 | 9일 | ...............................................|      31일   |

                                                                  | 생산수량 | 생산수량 |................................................................................................................|생산수량|

 

이것입니다.

 

ALTER PROCEDURE USP_P4428MA1_KO429 ( @PLANT_CD NVARCHAR(10), @YYYYMM NVARCHAR(07),
                                     @ITEM_CD NVARCHAR(10), @ITEM_GROUP NVARCHAR(10),
                                     @ITEM_ACCT_CD NVARCHAR(10) )
AS
SET @YYYYMM = REPLACE(@YYYYMM,'-','') 

SELECT  f.item_cd, f.item_nm, f.spec, f.basic_unit, j.minor_nm 'item_acct_nm', f.item_group_cd, i.item_group_nm,
        SUM(c.prod_qty_in_base_unit) Total,
        SUM(CASE WHEN day(c.report_dt) = 1 THEN c.prod_qty_in_base_unit ELSE 0 END ) '01',
        SUM(CASE WHEN day(c.report_dt) = 2 THEN c.prod_qty_in_base_unit ELSE 0 END ) '02',
        SUM(CASE WHEN day(c.report_dt) = 3 THEN c.prod_qty_in_base_unit ELSE 0 END ) '03',
        SUM(CASE WHEN day(c.report_dt) = 4 THEN c.prod_qty_in_base_unit ELSE 0 END ) '04',
        SUM(CASE WHEN day(c.report_dt) = 5 THEN c.prod_qty_in_base_unit ELSE 0 END ) '05',
        SUM(CASE WHEN day(c.report_dt) = 6 THEN c.prod_qty_in_base_unit ELSE 0 END ) '06',
        SUM(CASE WHEN day(c.report_dt) = 7 THEN c.prod_qty_in_base_unit ELSE 0 END ) '07',
        SUM(CASE WHEN day(c.report_dt) = 8 THEN c.prod_qty_in_base_unit ELSE 0 END ) '08',
        SUM(CASE WHEN day(c.report_dt) = 9 THEN c.prod_qty_in_base_unit ELSE 0 END ) '09',
        SUM(CASE WHEN day(c.report_dt) = 10 THEN c.prod_qty_in_base_unit ELSE 0 END ) '10',
        SUM(CASE WHEN day(c.report_dt) = 11 THEN c.prod_qty_in_base_unit ELSE 0 END ) '11',
        SUM(CASE WHEN day(c.report_dt) = 12 THEN c.prod_qty_in_base_unit ELSE 0 END ) '12',
        SUM(CASE WHEN day(c.report_dt) = 13 THEN c.prod_qty_in_base_unit ELSE 0 END ) '13',
        SUM(CASE WHEN day(c.report_dt) = 14 THEN c.prod_qty_in_base_unit ELSE 0 END ) '14',
        SUM(CASE WHEN day(c.report_dt) = 15 THEN c.prod_qty_in_base_unit ELSE 0 END ) '15',
        SUM(CASE WHEN day(c.report_dt) = 16 THEN c.prod_qty_in_base_unit ELSE 0 END ) '16',
        SUM(CASE WHEN day(c.report_dt) = 17 THEN c.prod_qty_in_base_unit ELSE 0 END ) '17',
        SUM(CASE WHEN day(c.report_dt) = 18 THEN c.prod_qty_in_base_unit ELSE 0 END ) '18',
        SUM(CASE WHEN day(c.report_dt) = 19 THEN c.prod_qty_in_base_unit ELSE 0 END ) '19',
        SUM(CASE WHEN day(c.report_dt) = 20 THEN c.prod_qty_in_base_unit ELSE 0 END ) '20',
        SUM(CASE WHEN day(c.report_dt) = 21 THEN c.prod_qty_in_base_unit ELSE 0 END ) '21',
        SUM(CASE WHEN day(c.report_dt) = 22 THEN c.prod_qty_in_base_unit ELSE 0 END ) '22',
        SUM(CASE WHEN day(c.report_dt) = 23 THEN c.prod_qty_in_base_unit ELSE 0 END ) '23',
        SUM(CASE WHEN day(c.report_dt) = 24 THEN c.prod_qty_in_base_unit ELSE 0 END ) '24',
        SUM(CASE WHEN day(c.report_dt) = 25 THEN c.prod_qty_in_base_unit ELSE 0 END ) '25',
        SUM(CASE WHEN day(c.report_dt) = 26 THEN c.prod_qty_in_base_unit ELSE 0 END ) '26',
        SUM(CASE WHEN day(c.report_dt) = 27 THEN c.prod_qty_in_base_unit ELSE 0 END ) '27',
        SUM(CASE WHEN day(c.report_dt) = 28 THEN c.prod_qty_in_base_unit ELSE 0 END ) '28',
        SUM(CASE WHEN day(c.report_dt) = 29 THEN c.prod_qty_in_base_unit ELSE 0 END ) '29',
        SUM(CASE WHEN day(c.report_dt) = 30 THEN c.prod_qty_in_base_unit ELSE 0 END ) '30',
        SUM(CASE WHEN day(c.report_dt) = 31 THEN c.prod_qty_in_base_unit ELSE 0 END ) '31'           
from p_production_order_header a with (nolock)    
         inner join p_production_order_detail b with (nolock) on a.prodt_order_no = b.prodt_order_no        
         inner join p_production_results c with (nolock) on b.opr_no = c.opr_no
                                                                                           and a.prodt_order_no = c.prodt_order_no 
                                                                                           and c.plant_cd LIKE @PLANT_CD  
                                                                                           and CONVERT(VARCHAR(06), c.report_dt, 112) = @YYYYMM   
         inner join b_item f with (nolock) on a.item_cd = f.item_cd
                                                                  and f.item_cd like @ITEM_CD
                                                                  and f.item_group_cd like @ITEM_GROUP
         left outer join b_item_group i with (nolock) on f.item_group_cd = i.item_group_cd
         inner join ( select minor_cd, minor_nm  from b_minor where MAJOR_CD = 'P1001') j on f.item_acct = j.minor_cd
                                                                                                                                                            and f.item_acct like @ITEM_ACCT_CD
GROUP BY f.item_cd, f.item_nm, f.spec, j.minor_nm, f.item_group_cd, i.item_group_nm, f.basic_unit