0

I am trying to create a table based on the results of Pivot I am using the Select * into newTempPivot but I am getting the following error

Must specify table to select from.
Msg 1038, Level 15, State 5, Line 118 An object or column name is missing or empty. For SELECT INTO statements, verify each column has a name. For other statements, look for empty alias names. Aliases defined as "" or [] are not allowed. Change the alias to a valid name.

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)


DECLARE @cols1 AS NVARCHAR(MAX),
    @queryt  AS NVARCHAR(MAX);

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

set @queryt =' select * into newTempPivot (SELECT date, ' + @cols1 + ' from 
            (
                select date
                    , amount
                    , category
                from temp
           ) x
            pivot 
            (
                 max(amount)
                for category in (' + @cols1 + ')
            ) p )'


execute(@queryt)

drop table temp

Please help me how can create a table with pivot results

Thom A
  • 88,727
  • 11
  • 45
  • 75
Hemachandra
  • 1
  • 1
  • 1
    `PRINT @queryt`/`SELECT @queryt` is your friend. But this is just a typographical error. YOu're missing a `FROM` after `newTempPivot` and an alias for your sub query. Alternatively, your second `SELECT` is in the wrong place, and it shouldn't be a subquery. – Thom A Jul 23 '20 at 08:53
  • Larnu, I want a new Table to be created using the pivot results. therefore I am using select * into newTempPivot (query result of pivot) – Hemachandra Jul 23 '20 at 09:02
  • Your `select * into newTempPivot` is actual being interpreted as a separate statement. As @Larnu said, you need to add `FROM` after your `into newTempPivot` and have an alias defined after your last `)` – Preben Huybrechts Jul 23 '20 at 09:03
  • It's not `SELECT * INTO {New Table} ({Sub query})` it's `SELECT {Columns} INTO {NewTable} FROM ...`. Again `PRINT @queryt/SELECT @queryt` is your friend. Your outer query is missing a `FROM`. – Thom A Jul 23 '20 at 09:05

0 Answers0