Table column split to two columns in sql?

Question

In sql server, I have once column in the table, i want to make the column in two column, in the other table [new table]. Can you help me.

------------------------
  Type
  ------------------------
  UserDefine

  UserDefine

  AutoGenerate

  AutoGenerate

  UserDefine
 -------------------------

The above is my column in one of my table now i want to make the column in two like UserDefine and Autogenerate column in different table

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

Like the above, help me thank you.

its not clear what you want to achieve be more clear – AnandPhadke Nov 22 '12 at 07:28 — AnandPhadke, Nov 22 '12 at 07:28

score 0 · Answer 1 · answered Nov 22 '12 at 07:31

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

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

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

Changing the where clause to the apropiate conditions to discriminate between the two types.

Mahmoud Gamal · Accepted Answer · 2012-11-22T13:24:09.723

0

Try this:

;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

edited Nov 22 '12 at 13:24

answered Nov 22 '12 at 07:38

Mahmoud Gamal

78,257
17
139
164

@MahmoudGamal why did you decide to use `HAVING` and not a `WHERE` clause? You don't have an aggregate function in the `HAVING` so there is no need for it. – Taryn Nov 22 '12 at 13:19
@bluefeet - Yes, you are right. Actually I was testing it with an aggregate function then I left it this way. But is there any other way to get this pivot working without the `ROW_NUMBER()` and `WHERE rownum <= 2` with the `PIVOT` operator, it gets only two values since it groups with the `type` column, thats why I used the `ROW_NUMBER()` then `WHERE rownum < 2` to eliminate an extra `NULL` value. – Mahmoud Gamal Nov 22 '12 at 13:26
You would have to use `row_number()` to still implement the `PIVOT` but here is a `PIVOT` version -- http://sqlfiddle.com/#!3/cf6de/23 – Taryn Nov 22 '12 at 13:30

Table column split to two columns in sql?

2 Answers2

SQL Fiddle Demo

Linked