Inserting data into table using select query

Question

Hi

I have one table like
  Columsns

ID(Primary key)    AP        MP      TN     MP      ..... These are all state names as columns

my query returning the data like

Statename       Count
AP                     10
MP                     20
TN                      30

How can i insert  this result set in to table in sql server.

Please help

Regards
Ajay

Jignesh Trivedi · Answer

Hi Ajay you can solved your problem using PIVOT Query in SQL For this you must have fix column means APMP ect are fixed. Now I am assume your table just like table created by Senthilkumar( in above post). try.... SELECT * FROM (SELECT StateName COUNT(*) AS [Count1] FROM States GROUP BY StateName ) ps PIVOT ( sum (Count1) FOR StateName IN ( [AP] [MP][TN]) ) AS pvt please refer http://blog.sqlauthority.com/2008/06/07/sql-server-pivot-and-unpivot-table-examples/ hope this help.

Senthilkumar · Answer

CREATE TABLE States ( ID INT IDENTITY(11) PRIMARY KEY StateName VARCHAR(100) ) INSERT INTO States(StateName) VALUES('TN') INSERT INTO States(StateName) VALUES('MP') INSERT INTO States(StateName) VALUES('AP') INSERT INTO States(StateName) VALUES('TN') INSERT INTO States(StateName) VALUES('MP') SELECT StateName COUNT(*) AS [Count] FROM States GROUP BY StateName The result can be stored in the two way. One way is to create the another table to hold the query results. It will be stored permanently. CREATE TABLE StatesCount ( StateName VARCHAR(100) StateCount INT ) INSERT INTO StatesCount(StateNameStateCount) SELECT StateName COUNT(*) AS [Count] FROM States GROUP BY StateName -- SELECT * FROM StatesCount The another way is you can hold the results in the temp table. If it is local temp table then only available to the current session. If it is global then available across all the session users in sql server. SELECT StateName COUNT(*) AS [Count] INTO #tmpStateCount FROM States GROUP BY StateName --SELECT * FROM #tmpStateCount If it is helps you then please mark as accepted answer.