CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
Wilheim
Wilheim

Reputation: 123

How to create a table from a pivot table?

I have a table like this,

enter image description here

and I have pivoted it to the following with the code,

select * 
from 
(
  select  *
  from table1
) a
pivot 
(
avg(col3)
 for col2 in ([a],[b],[c],[d])
) as pv;

enter image description here

How can I create a new table from the pivot table pv? I have tried select * into newTable, but it doesn't seem to work.

Upvotes: 2

Views: 8833

Answers (3)

Cetin Basoz
Cetin Basoz

Reputation: 23837

The simpliest way to do that is to use "into" clause as you guessed originally. Since you didn't share your code, we don't know what you might have done wrong. Just add "into tableName" to your own pivoting code:

select * into myPivotedTable 
from 
(
  select  *
  from table1
) a
pivot 
(
avg(col3)
 for col2 in ([a],[b],[c],[d])
) as pv;

PS: I assume you have permission to create a table.

Upvotes: 0

Jibin Balachandran
Jibin Balachandran

Reputation: 3441

Try this:

Make sure the columns in your select is equal to the columns in the new table.

INSERT INTO NewTable
select * 
from 
(
  select  *
  from table1
) a
pivot 
(
avg(col3)
 for col2 in ([a],[b],[c],[d])
) as pv;

If you don't want an actual table, you want this data to be accessed throughout your query then you can make use of a temporary table #TempTbl and then access data from #TempTbl.

INSERT INTO #TempTbl
select * 
from 
(
  select  *
  from table1
              ) a
 pivot 
 (
   avg(col3)
   for col2 in ([a],[b],[c],[d])
                                  ) as pv;

Upvotes: 0

Shakeer Mirza
Shakeer Mirza

Reputation: 5110

You can create it on the fly with INTO

SELECT * INTO #TAB
FROM 
(
  SELECT  *
  FROM TABLE1
) A
PIVOT 
(
AVG(COL3)
 FOR COL2 IN ([A],[B],[C],[D])
) AS PV;

With Schema provided by Chanukya. (Worked for me)

CREATE TABLE #D
(
COL1 INT,
COL2 VARCHAR(10),
COL3 INT
)
INSERT INTO #D VALUES
(1,'A',11),
(2,'B',22),
(3,'C',33),
(4,'D',44)

SELECT * INTO #PIVOT_RESULT
FROM (
SELECT COL1, COL2, COL3 FROM #D
)AS A
PIVOT 
(
AVG(COL3)
FOR COL2 IN ([A],[B],[C],[D])
) AS PV;


SELECT * FROM #PIVOT_RESULT

Upvotes: 2

Related Questions