CODEWITHSUNDEEP
sqlsql-server
Siva Reddy
Siva Reddy

Reputation: 37

copy one table to another table with diffrent columns

I have a TableA columns are (id,name,A,B,C,p_id)

i want convert TableA to TableB, TableB columns are (id,name,alphabets,alphabets_value,p_id)

Record in TableA

id | name | A | B | C | p_id
1  | xyz  | a | b |   | 1
2  | opq  | a`| b`| c`| 1

Expected In TableB

u_id | id | name | alphabets | alphabets_value | p_id
  1  | 1  | xyz  |    A      |       a         | 1
  2  | 1  | xyz  |    B      |       b         | 1
  3  | 2  | opq  |    A      |       a`        | 1    
  4  | 2  | opq  |    B      |       b`        | 1
  5  | 2  | opq  |    C      |       c`        | 1

i want TableB output currently using Microsoft SQL

Upvotes: 0

Views: 57

Answers (1)

Caius Jard
Caius Jard

Reputation: 74740

This is an unpivot, probably most easily explained by a UNION ALL:

SELECT id, name, 'A' as alphabets, a as alphabets_value, p_id
UNION ALL
SELECT id, name, 'B' as alphabets, b as alphabets_value, p_id
UNION ALL
SELECT id, name, 'C' as alphabets, c as alphabets_value, p_id

You can then WHERE to remove the nulls from this, and ROW_NUMBER to give yourself a fake U_id:

SELECT ROW_NUMBER() OVER(ORDER BY id, alphabets) as u_id, x.*
FROM
(
  SELECT id, name, 'A' as alphabets, a as alphabets_value, p_id
  UNION ALL
  SELECT id, name, 'B' as alphabets, b as alphabets_value, p_id
  UNION ALL
  SELECT id, name, 'C' as alphabets, c as alphabets_value, p_id
)
WHERE
  x.alphabets_value IS NOT NULL

Once you get to having a result set you want, INSERT INTO, UPDATE FROM or MERGE to get it into table B is quite trivial

Upvotes: 2

Related Questions