0

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

Hasan Patel
  • 412
  • 7
  • 19
Siva Reddy
  • 37
  • 6
  • First figure out which RDBMS you're using. Then see https://meta.stackoverflow.com/questions/333952/why-should-i-provide-a-minimal-reproducible-example-for-a-very-simple-sql-query – Strawberry Jul 21 '20 at 18:11
  • Microsoft SQL Server – Siva Reddy Jul 21 '20 at 18:16
  • you need to UPDATE TableB, SET the changes, FROM TableA and JOIN the column you need to change and specify a condition. You can try reading from this answer [Copy Data from one column to another Column in different tables](https://stackoverflow.com/questions/3361768/copy-data-from-one-column-to-other-column-which-is-in-a-different-table) – Hasan Patel Jul 21 '20 at 18:24
  • 1
    Does this answer your question? [Copy data from one column to other column (which is in a different table)](https://stackoverflow.com/questions/3361768/copy-data-from-one-column-to-other-column-which-is-in-a-different-table) – Hasan Patel Jul 21 '20 at 18:25

1 Answers1

2

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

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
  • Hi @Cauis Jard Thanks for your quick response, but this is not what i expect what if i have N number of records – Siva Reddy Jul 21 '20 at 18:41
  • I need Recorsive answer – Siva Reddy Jul 21 '20 at 18:45
  • How can you have N number of records when you only have 3 columns that need unpivoting? – Caius Jard Jul 21 '20 at 19:08
  • Hi, @Causis Jard this is not the answer for my question, But i git the answer with your key word i.e UNPIVOT thanks for that i will give upvote to you , this is the answer to my question [https://stackoverflow.com/questions/19055902/unpivot-with-column-name] – Siva Reddy Jul 22 '20 at 06:53
  • UNPIVOT is SQLServer specific (well, some other DB support it too, but not all) syntax, it doesn't work in all DB. Generally when answering SQL questions I aim to provide answers that just use standard SQL syntax that should works in all DB with very little modification. The query I gave above will produce the same output as an UNPIVOT - see https://stackoverflow.com/questions/15184381/mysql-turn-table-into-different-table for example; MySQL doesn't have UNPIVOT so it uses a `SELECT x UNION ALL SELECT y UNION ALL SELECT z` pattern, one union for each column to be unpivoted – Caius Jard Jul 22 '20 at 07:10
  • What you need to undertstand is UNPIVOT isn't magical; where that query says `Marks for details in (Maths, Science, English)` it means it can unpivot 3 columns into 3 rows. In this form it would be `SELECT maths UNION ALL SELECT science UNION ALL SELECT english` - 3 columns. If there was another column, physics, then the UNPIVOT form needs 4 columns writing in and so does the UNION way.. `Marks for details in (Maths, Science, English, Physics)` vs `SELECT math UNION ALL SELECT english UNION ALL SELECT science UNION ALL SELECT physics`.. – Caius Jard Jul 22 '20 at 07:18