How can use PivotTable in SQL Server with temporary table

Question

Very new to SQL Server, at this time I want to use a query with a Pivot table but something is bat, Is there some one could be help me to find my error I appreciate. Regards In the first part I create a dummy table called DateTemp in the second part, I join two tables the DateTemp and datcpc tables, now I want to use a pivot table, but I get an error near to pivot section.

My code:

// First Part
DECLARE @StartDate datetime 
DECLARE @EndDate datetime 

SELECT @StartDate = '2018-01-01',  @EndDate = '2018-11-08' 
SELECT @StartDate = @StartDate - (DATEPART(DD, @StartDate) - 1) 

DECLARE @DateTemp TABLE (TheDate DATETIME) 

WHILE (@StartDate <= @EndDate) 
BEGIN 
    INSERT INTO @DateTemp 
    VALUES (@StartDate) 

    SELECT @StartDate = DATEADD(DAY, 1, @StartDate) 
END 

// Second part
SELECT 
    CYALA, CYALB 
FROM
    (SELECT DISTINCT 
         fechab 'FECHA', clapla 'CLAVEPLANTA', clapro 'CLAVE PRODUCTO', 
         CAST(SUM(cantid) AS NUMERIC(9, 2)) 'ACTIVIDAD' 
     FROM 
         (datcpc 
     LEFT JOIN
         (SELECT TheDate 
          FROM @DateTemp 
          WHERE TheDate >= '05/01/2018') NT ON datcpc.fechab = NT.TheDate)
     WHERE 
         datcpc.fechab >= '01/05/2018' 
         AND datcpc.fechab >= '01/05/2018' 
         AND (clapla = 'CYALA' OR clapla = 'CYALB' OR clapla = 'CYAZC')
         AND datcpc.tipflu = 'C'
    GROUP BY 
        fechab, clapla, clapro) AS SOURCE 
PIVOT
    (SUM(SOURCE.ACTIVIDAD) 
         FOR SOURCE.CLAVEPLANTA IN (CYALA, CYALB)
    ) AS PIVOTABLE

Test the suggested answers, without success. Try a simpler example and even then the error is set as "System.Data.SqlClient.SqlException: 'Line 1: Incorrect syntax near 'PIVOT'.'", I do not know if it is the server configuration or something about it. I use a connection to the server via Visual Studio 2017 via SqlConnection my new easier codes is listed below

SELECT DISTINCT [CLAVEPLANTA], [ACTIVIDAD]
FROM( 
    SELECT fechab AS [FECHA], clapla AS [CLAVEPLANTA], CAST(SUM(cantid) AS NUMERIC(9,2) ) AS [ACTIVIDAD] FROM datcpc"
    WHERE datcpc.fechab >= '01/05/2018'         
    AND (clapla='CYALA' OR clapla='CYALB' OR clapla='CYAZC')
    GROUP BY fechab, clapla 
 ) AS [SO] 
PIVOT(SUM([SO.ACTIVIDAD]) FOR [SO.CLAVEPLANTA] IN ([CYALA], [CYALB])) AS [PVT];

I really apreciate you help

For any error message please include it - in full - in the question. Also it is not clear (to us) what you expect the query to do. If you provide an example of what the result should look like then perhaps we can offer more advice. Right now I would have to guess that what you want as columns. Reading these may help too [Provide a `Minimal Complete Verifiable Example` (MCVE)](https://stackoverflow.com/help/mcve) and [Why should I provide a MCVE](https://meta.stackoverflow.com/questions/333952/why-should-i-provide-an-mcve-for-what-seems-to-me-to-be-a-very-simple-sql-query) — Paul Maxwell, Nov 10 '18 at 02:54

Paul Maxwell · Answer 1 · 2018-11-10T03:53:35.950

I am going to provide 2 variants of a "dynamic pivot" which I hope will assist:

Some sample data:

create table temp
(
    date datetime,
    category varchar(3),
    amount money
)

insert into temp values ('1/1/2012', 'ABC', 1000.00)
insert into temp values ('2/1/2012', 'DEF', 500.00)
insert into temp values ('2/1/2012', 'GHI', 800.00)
insert into temp values ('2/10/2012', 'DEF', 700.00)
insert into temp values ('3/1/2012', 'ABC', 1100.00)

Now a query that will "pivot" that data into columns per day:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(convert(char(10), c.[date],121)) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT category, ' + @cols + ' from 
            (
                select convert(char(10), [date],121) [date]
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 sum(amount)
                for [date] in (' + @cols + ')
            ) p '

execute(@query)

The result looks like this:

+----------+------------+------------+------------+------------+
| category | 2012-01-01 | 2012-02-01 | 2012-02-10 | 2012-03-01 |
+----------+------------+------------+------------+------------+
| ABC      | 1000,0000  | NULL       | NULL       | 1100,0000  |
| DEF      | NULL       | 500,0000   | 700,0000   | NULL       |
| GHI      | NULL       | 800,0000   | NULL       | NULL       |
+----------+------------+------------+------------+------------+

Now, this query "pivots" category into columns:

DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX);

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.category) 
            FROM temp c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT convert(char(10),date,121) date, ' + @cols + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols + ')
            ) p '

execute(@query)

The result looks like this:

+------------+-----------+----------+----------+
|    date    |    ABC    |   DEF    |   GHI    |
+------------+-----------+----------+----------+
| 2012-01-01 | 1000,0000 | NULL     | NULL     |
| 2012-02-01 | NULL      | 500,0000 | 800,0000 |
| 2012-02-10 | NULL      | 700,0000 | NULL     |
| 2012-03-01 | 1100,0000 | NULL     | NULL     |
+------------+-----------+----------+----------+

see these as a live demo here: 2 variants of TSQL pivot tables
origin of these examples Dynamic SQL PIVOT

some suggestions:

--Second part

SELECT
    CYALA,CYALB -- you need to specify more columns here
FROM (
    SELECT DISTINCT -- distinct is not needed when grouping, remove this
        fechab                             'FECHA'
       ,clapla                             'CLAVEPLANTA'
       ,clapro                             'CLAVE PRODUCTO'
       ,CAST(SUM(cantid) AS numeric(9, 2)) 'ACTIVIDAD'
    /* if you are creating the temporal table to get EVERY date
    , then @DateTemp should be the base table, and left join the [datcpc] */
    FROM (datcpc
    LEFT JOIN (      
            SELECT
                TheDate
            FROM @DateTemp
            WHERE TheDate >= '05/01/2018' -- use YYYMMDD or YYYY-MM-DD consistently
        ) NT
        ON datcpc.fechab = NT.TheDate)
    WHERE datcpc.fechab >= '01/05/2018'
    AND datcpc.fechab >= '01/05/2018'      -- repeated, not needed
    AND clapla IN('CYALA','CYALB','CYAZC') -- use IN()
    AND datcpc.tipflu = 'C'
    GROUP BY
        fechab ,clapla ,clapro
) AS SOURCE
PIVOT (
    SUM(SOURCE.ACTIVIDAD) 
    FOR SOURCE.CLAVEPLANTA IN (CYALA, CYALB) -- did you want dates here? (as the pivot columns) 
) AS PIVOTABLE

I redo the code but this time a easier form, but the error does not appers — Salvo79, Nov 12 '18 at 22:04
Nice to know, but as I cannot see anything I cannot offer anything in reply. You can EDIT you original question (but, don't change the question, just ADD to it. Perhaps show us the query you just tried and the result). — Paul Maxwell, Nov 12 '18 at 22:19

How can use PivotTable in SQL Server with temporary table

1 Answers1