Turn Row Columns into sql

Asked

Viewed 37 times

0

Good morning Personal,

Have the following query:

DECLARE @DTINICIAL AS DATE
DECLARE @DTFINAL AS  DATE

SET @DTINICIAL = '202101[![inserir a descrição da imagem aqui][1]][1]01'
SET @DTFINAL = '20210630'

SELECT 

    SUM(CONVERT(DECIMAL(18,2),CASE WHEN ZB0.ZB0_NATURE ='910074' THEN ZB0.ZB0_VLTRAN else 0 END)) CRED_MERC_LOGISTICA,
    SUM(CONVERT(DECIMAL(18,2),CASE WHEN ZB0.ZB0_NATURE ='910075' THEN ZB0.ZB0_VLTRAN else 0 END)) CRED_MERC_INDUSTRIA,
    SUM(CONVERT(DECIMAL(18,2),CASE WHEN ZB0.ZB0_NATURE ='910076' THEN ZB0.ZB0_VLTRAN else 0 END)) ESTORNO_ICMS,
    SUM(CONVERT(DECIMAL(18,2),CASE WHEN ZB0.ZB0_NATURE ='910077' THEN ZB0.ZB0_VLTRAN else 0 END)) CRED_GARANTIA,
    SUM(CONVERT(DECIMAL(18,2),CASE WHEN ZB0.ZB0_NATURE ='910078' THEN ZB0.ZB0_VLTRAN else 0 END)) CRED_CARTAO_CREDITO
FROM 

    ZB0010 ZB0
WHERE
    1=1
    AND ZB0.ZB0_DATA BETWEEN @DTINICIAL AND @DTFINAL

Note: As the attached image I need to invert the result or turn this column into a row so the table will have only 2 columns account name and the value of each one..

Comment: thanks for your attention

inserir a descrição da imagem aqui

sql

  • first, ah the classic where 1=1, haven’t seen that in a long time :) sum a separate select and merge everything with union and that’s it, it’s going to result in different lines

2 answers

2

Usually it’s harder to turn rows into columns. You can break the query and merge with UNION ALL, I don’t know if it is efficient for the amount of data you have, but nothing like testing.

I made only for 2 of the values you want, just complete with others. Can validate please?

                DECLARE @DTINICIAL AS DATE
                DECLARE @DTFINAL AS  DATE

                SET @DTINICIAL = '202101[![inserir a descrição da imagem aqui][1]][1]01'
                SET @DTFINAL = '20210630'

                SELECT 
                    'CRED_MERC_LOGISTICA' AS DESCRICAO,
                    SUM(CONVERT(DECIMAL(18,2),CASE WHEN ZB0.ZB0_NATURE ='910074' THEN ZB0.ZB0_VLTRAN else 0 END)) CRED_MERC_LOGISTICA,
                FROM 

                    ZB0010 ZB0
                WHERE
                    1=1
                    AND ZB0.ZB0_DATA BETWEEN @DTINICIAL AND @DTFINAL

                UNION ALL

                SELECT 
                    'CRED_MERC_INDUSTRIA' AS DESCRICAO,
                    SUM(CONVERT(DECIMAL(18,2),CASE WHEN ZB0.ZB0_NATURE ='910075' THEN ZB0.ZB0_VLTRAN else 0 END)) CRED_MERC_INDUSTRIA,
                FROM 

                    ZB0010 ZB0
                WHERE
                    1=1
                    AND ZB0.ZB0_DATA BETWEEN @DTINICIAL AND @DTFINAL

0

Mario,

Thank you for answering, but imagine that this information will be dynamic so it will not roll using the Union I had even thought about it works but the query will get huge and will affect the performance of the query. But thanks for answering.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.