pivot error

arkiboys 9,666 Reputation points
2022-03-11T13:12:08.46+00:00

Hello,
Can you see how to solve this query please?

error:
== SQL ==
select
*
from(
SELECT accountDate,tense,dynamic
FROM tempdata) DATA
PIVOT (SUM(dynamic ) FOR tense IN ([ução nér],[ção té],[Báa],[ações])

----------------------------------------^^^

select
*
from(
SELECT accountDate,tense,dynamic
FROM tempdata) DATA
PIVOT (SUM(dynamic ) FOR tense IN ([ução nér],[ção té],[Báa],[ações])
) AS RESULT

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,563 questions
0 comments No comments
{count} votes

Accepted answer
  1. Naomi 7,361 Reputation points
    2022-03-11T14:50:44.79+00:00

    Code worked for me:

    DECLARE @t TABLE (ShiftDate DATE, TIPO NVARCHAR(100), dmt INT);
    
    INSERT INTO @t (ShiftDate, TIPO, dmt)
    VALUES
     ('20200101' 
      , N'ução nér' -- TIPO - nvarchar(100)
      , 10 -- dmt - int
     ),
     ('20200101' 
      , N'ução tér' -- TIPO - nvarchar(100)
      , 40 -- dmt - int
     ),
     ('20200101' 
      , N'Outentações' -- TIPO - nvarchar(100)
      , 1000 -- dmt - int
     ),
     ('20200102' 
      , N'imá' -- TIPO - nvarchar(100)
      , 7 -- dmt - int
     ),
     ('20200103' 
      , N'imá' -- TIPO - nvarchar(100)
      , 12 -- dmt - int
     );
    
    SELECT * FROM (
    SELECT SHIFTDATE,TIPO,DMT
    FROM @t
    ) AS DATA
    PIVOT
    (
    SUM(DMT)
    FOR TIPO IN ([ução nér],[ução tér],[imá],[Outentações])
    ---------------^^^
    ) AS RESULT
    
    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Olaf Helper 41,411 Reputation points
    2022-03-11T13:23:35.693+00:00

    In your first query the alias is missing, that causes the error; in second you used "AS Result" for the alias.
    See
    https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver15#basic-pivot-example