sqlsql-serverpivotpivot-table

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.

Solution

  • 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