mssql excel null 도와주시면 감사하겠습니다! 0 5 185

by 김성진 [SQLServer] mssql sql excel [2021.02.17 11:57:34]


데이터가 나오면 poi를 이용하여 엑셀파일로 저장을 하게 해둔 상황입니다

 

지금의 쿼리문 입니다

 

SELECT
    *
FROM
    (
    SELECT
        ROW_NUMBER() OVER (
        ORDER BY measurement_date DESC, measurement_time DESC ) AS rownum ,
        (CASE
            WHEN device_plot = '11' THEN 'Plot1'
            WHEN device_plot = '22' THEN 'Plot2'
            WHEN device_plot = '33' THEN 'Plot3'
            WHEN device_plot = '44' THEN 'Plot4'
        END)as deviceplot , CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate 
                          , STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':')         measurementtime 
                          , MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value  END) temperaturevalueT 
                          , MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value  END) temperaturevalueM 
                          , MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value  END) temperaturevalueB 
                          , MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value  END) humidityvalueT 
                          , MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value  END) humidityvalueM
    FROM
        smarcle_data_log
    WHERE
        1 = 1
        AND device_plot_tmb IN ('1', '2', '3')
        AND(SUBSTRING(measurement_time, 3, 2) % 10)= 0
        AND measurement_date BETWEEN REPLACE(20210115, '-', '') AND REPLACE(20210115, '-', '')
    GROUP BY
        device_plot, measurement_date, measurement_time)a

 

쿼리문 입니다..

이상태로 엑셀 다운로드를 하게 되면 값은 잘 나오나 빈칸이 나오는곳이 null 값이 표출되어서 도움을 주시면 감사하겠습니다

!

by 김성진 [2021.02.17 12:07:10]

CASE 문을 써서 똑같이 

SELECT
    *
FROM
    (
    SELECT
        ROW_NUMBER() OVER (
        ORDER BY measurement_date DESC, measurement_time DESC ) AS rownum ,
        (CASE
            WHEN device_plot = '11' THEN 'Plot1'
            WHEN device_plot = '22' THEN 'Plot2'
            WHEN device_plot = '33' THEN 'Plot3'
            WHEN device_plot = '44' THEN 'Plot4'
            WHEN temperature_value = 'NULL' THEN ''
        END)as deviceplot , CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate 
                          , STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':')         measurementtime 
                          , MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value  END) temperaturevalueT 
                          , MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value  END) temperaturevalueM 
                          , MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value  END) temperaturevalueB 
                          , MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value  END) humidityvalueT 
                          , MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value  END) humidityvalueM
    FROM
        smarcle_data_log
    WHERE
        1 = 1
        AND device_plot_tmb IN ('1', '2', '3')
        AND(SUBSTRING(measurement_time, 3, 2) % 10)= 0
        AND measurement_date BETWEEN REPLACE(20210115, '-', '') AND REPLACE(20210115, '-', '')
    GROUP BY
        device_plot, measurement_date, measurement_time, temperature_value)a

 

처리를 해보았는데  값이 제대로 나오지 않아서 도움을 구합니다 ㅠㅠ


by 마농 [2021.02.17 13:47:10]
SELECT *
  FROM (SELECT ROW_NUMBER() OVER(
               ORDER BY measurement_date DESC, measurement_time DESC) AS rownum
             , CASE device_plot
                    WHEN '11' THEN 'Plot1'
                    WHEN '22' THEN 'Plot2'
                    WHEN '33' THEN 'Plot3'
                    WHEN '44' THEN 'Plot4'
                    ELSE '' END AS deviceplot
             , CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111)   measurementdate
             , STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':')          measurementtime
             , CONCAT(MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END), '') temperaturevalueT
             , CONCAT(MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END), '') temperaturevalueM
             , CONCAT(MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END), '') temperaturevalueB
             , CONCAT(MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value    END), '') humidityvalueT
             , CONCAT(MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value    END), '') humidityvalueM
          FROM smarcle_data_log
         WHERE 1=1
           AND device_plot_tmb IN ('1', '2', '3')
           AND SUBSTRING(measurement_time, 3, 2) % 10 = 0
           AND measurement_date BETWEEN '20210115' AND '20210115'
         GROUP BY device_plot, measurement_date, measurement_time
        ) a
;

 


by 김성진 [2021.02.17 14:58:00]

감사합니다!

지금 쓰고있는 mssql 버전이 2008 버전으로 알고있는데 concat 는 14버전 이후에 생긴 함수라 사용을 못한다고 하는데 혹시 다른 방법이 있는지 좀 알 수 있을까요?? 감사합니다


by 마농 [2021.02.17 18:52:05]
SELECT *
  FROM (SELECT ROW_NUMBER() OVER(
               ORDER BY measurement_date DESC, measurement_time DESC) AS rownum
             , CASE device_plot
                    WHEN '11' THEN 'Plot1'
                    WHEN '22' THEN 'Plot2'
                    WHEN '33' THEN 'Plot3'
                    WHEN '44' THEN 'Plot4'
                    ELSE '' END AS deviceplot
             , CONVERT(CHAR(10), CONVERT(DATETIME, measurement_date), 111) measurementdate
             , STUFF(STUFF(measurement_time, 3, 0, ':'), 6, 0, ':')        measurementtime
             , ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '1' THEN temperature_value END) AS VARCHAR), '') temperaturevalueT
             , ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '2' THEN temperature_value END) AS VARCHAR), '') temperaturevalueM
             , ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '3' THEN temperature_value END) AS VARCHAR), '') temperaturevalueB
             , ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '1' THEN humidity_value    END) AS VARCHAR), '') humidityvalueT
             , ISNULL(CAST(MAX(CASE device_plot_tmb WHEN '2' THEN humidity_value    END) AS VARCHAR), '') humidityvalueM
          FROM smarcle_data_log
         WHERE 1=1
           AND device_plot_tmb IN ('1', '2', '3')
           AND SUBSTRING(measurement_time, 4, 1) = '0'
           AND measurement_date BETWEEN '20210115' AND '20210115'
         GROUP BY device_plot, measurement_date, measurement_time
        ) a
;

 


by 김성진 [2021.02.19 10:22:02]

역시 god 마농님 감사합니다 ㅠㅠ 덕분에 잘 해결된 것 같습니다! 감사합니다!

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입