How can I do a PIVOT with Dynamic columns?

Question

How can I do a PIVOT with Dynamic columns?

Navigation

#1 by (1 votes)

2

I have a problem when generating a query using the PIVOT function of SQL - server 2008. The query itself has no disadvantages, since the values it throws are correct. For EMP3 the query throws totally NULL or 0, this is correct, but I need to generate a dynamic query that does not show the column whose sum is 0.

The query is detailed below:

    SELECT 
    [Fecha], [Cantidad],
    ISNULL([EMP1],0) AS [EMP1],
    ISNULL([EMP2],0) AS [EMP2],
    ISNULL([EMP3],0) AS [EMP3],
    ISNULL([EMP4],0) AS [EMP4]
    FROM(   
        SELECT 
        CONVERT (VARCHAR(10),de.FECHA, 103) As [Fecha], 
        ci.COD_EMP,
        SUM(bs.IMPORTE) as [Importe],
        SUM (CASE WHEN bs.CANTIDAD <> 0 THEN 1 ELSE 0 END) AS [Cantidad]
        FROM TABLA1 lc (NOLOCK)
        JOIN TABLA2 bs (NOLOCK) ON lc.ID1 = bs.ID1
        JOIN TABLA3 so (NOLOCK) ON bs.soc_id = so.soc_id
        JOIN TABLA4 ci (NOLOCK) ON ci.COD_EMP = '<<funcion que arroja            COD_EMP sobre lc.ID>>'
        JOIN TABLA5 de (NOLOCK) ON bs.ID2 = de.ID2
        JOIN TABLA6 co (NOLOCK) ON bs.ID3 = co.ID3
        JOIN TABLA7 cb (NOLOCK) ON co.CTA = cb.CTA
        JOIN TABLA8 su (NOLOCK) ON cb.SUC = su.SUC
        WHERE co.CO_ID = '11111'
        AND bs.ESTADO in (6,7)
        AND de.FECHA BETWEEN CONVERT (DATETIME,'2017-10-24') AND CONVERT         (DATETIME,'2017-10-27')
        AND '<<FUNCION>>' <> so.COD_EMP
        AND lc.SERV = bs.SERV
        GROUP BY FECHA , '<<FUNCION>>'),ci.COD_EMP
    ) nuevatabla
    PIVOT (SUM(IMPORTE) FOR COD_EMP in ([EMP1], [EMP2],[EMP3], [EMP4])) AS         pvt 
    ORDER BY 1

The result is the following:

    Fecha   Cantidad    EMP1    EMP2    EMP3    EMP4
    24/10/2017  1       0.00    5.96    0.00    0.00
    24/10/2017  4       66.71   0.00    0.00    0.00
    24/10/2017  8       0.00    0.00    0.00    4.05

How can I write this query to avoid showing columns that are completely 0 ?. As an extra comment, the EMP3 column can have data depending on the date and any other column can not.

sql-server pivot

asked by ISNULL 04.11.2017 в 15:06

source

1 answer

"NoReverseMatch at" error with "django.contrib.auth.views.login" tests Error in data output C

score 1 · Accepted Answer

First, answering your question "How can I write this query to avoid showing columns that are completely 0?" : You can not, there is no way to remove columns of a query dynamically. Now, nothing prevents you from writing a dynamic query and executing it using sp_executsql .

To be able to do it, the steps would be:

Insert your query in a temporary table

Verify that columns have only 0 and assemble a dynamic query that eliminates columns

Execute the query

Point 1 is simple, I will not extend it, or create the temporary table and do a INSERT INTO of your query or make a SELECT INTO #Temp of your query. Let's assume that we already have the query in a temporary table, so that the example is functional, we do this:

CREATE TABLE #Temp (
    Fecha       DATETIME,
    Cantidad    INT,
    EMP1        NUMERIC(15,2),
    EMP2        NUMERIC(15,2),
    EMP3        NUMERIC(15,2),
    EMP4        NUMERIC(15,2)
)

INSERT INTO #Temp (Fecha,Cantidad,EMP1,EMP2,EMP3,EMP4)
VALUES  ('20171024',1,0.00,5.96,0.00,0.00),
        ('20171024',4,66.71,0.00,0.00,0.00),
        ('20171024',8,0.00,0.00,0.00,4.05)

We are going to create a dynamic SQL statement excluding the columns that we do not want:

DECLARE @SQL NVARCHAR(MAX)

SELECT  @SQL = 'SELECT Fecha,Cantidad,' 

SELECT  @SQL = @SQL + 
        CASE WHEN D.EMP1 = 1 AND V.EMP1 = 0 THEN '' ELSE 'EMP1,' END +
        CASE WHEN D.EMP2 = 1 AND V.EMP2 = 0 THEN '' ELSE 'EMP2,' END +
        CASE WHEN D.EMP3 = 1 AND V.EMP3 = 0 THEN '' ELSE 'EMP3,' END +
        CASE WHEN D.EMP4 = 1 AND V.EMP4 = 0 THEN '' ELSE 'EMP4,' END
      FROM ( SELECT  COUNT(DISTINCT EMP1) AS 'EMP1',
                     COUNT(DISTINCT EMP2) AS 'EMP2',
                     COUNT(DISTINCT EMP3) AS 'EMP3',
                     COUNT(DISTINCT EMP4) AS 'EMP4'
                     FROM #Temp
            ) D
       CROSS JOIN ( SELECT TOP 1 EMP1, EMP2, EMP3, EMP4
                          FROM #Temp
            ) V
SELECT @SQL = LEFT(@SQL , LEN(@SQL ) -1 ) + ' FROM #Temp'

The logic is simple, if the column has a single value, and the value of the first row is 0 we can assume that the entire column has only 0. If we review the statement @SQL we see more clearly what we achieved: SELECT Fecha,Cantidad,EMP1,EMP2,EMP4 FROM #Temp , we successfully exclude the column EMP3 . Now it only remains to execute it:

EXECUTE sp_executesql @SQL

The exit, I understand is what you expect:

+----------------------+----------+-------+------+------+
| Fecha                | Cantidad | EMP1  | EMP2 | EMP4 |
+----------------------+----------+-------+------+------+
| 2017-10-24T00:00:00Z | 1        | 0     | 5.96 | 0    |
+----------------------+----------+-------+------+------+
| 2017-10-24T00:00:00Z | 4        | 66.71 | 0    | 0    |
+----------------------+----------+-------+------+------+
| 2017-10-24T00:00:00Z | 8        | 0     | 0    | 4.05 |
+----------------------+----------+-------+------+------+