Column column split into two columns in sql?

Question

In sql server, I have a column in a table, I want to make a column into two columns, in another table [new table]. Can you help me.

------------------------ Type ------------------------ UserDefine UserDefine AutoGenerate AutoGenerate UserDefine ------------------------- 

The above my column in one of my tables now I want to make a column in two, like the UserDefine and Autogenerate column in another table

  ----------------------------------- UserDefine | AutoGener | ------------------------------------| UserDefine | AutoGenerate | | | UserDefine | AutoGenerate | --------------------------------------- 

As above, help me thank you.

Answer 1

Try the following:

 ;WITH CTE AS ( SELECT *, ROW_NUMBER() OVER(PARTITION BY [Type] ORDER BY [Type]) rownum FROM Table1 ) SELECT MAX(CASE WHEN [Type] = 'AutoGenerate' THEN [Type] END) AS 'AutoGenerate', MAX(CASE WHEN [Type] = 'UserDefine' THEN [Type] END) AS 'UserDefine' FROM CTE WHERE Rownum <= 2 GROUP BY Rownum; 

SQL Fiddle Demo

Answer 2

I would use two inserts, one for "UserDefine" and one for "AutoGenerate"

 INSERT INTO NewTable (UserDefine) SELECT Type FROM OldTable WHERE Type = "UserDefine" INSERT INTO NewTable (UserDefine) SELECT Type FROM OldTable WHERE Type = "AutoGenerate" 

Changing where clause for apropiate conditions to distinguish between two types.