SQL Server combine multiple column into 1 column on the same table-CodePudding

I have an example table like this :

  id |  Name   | code1 | code2
   ------------------------------
  1  |  John   |  001  |  AC2
  2  |  Anna   |  002  |  AH5
  3  |  Brad   |  003  |  BB1

I want to combine column code1 and code2 (and another column if any) into 1 column and duplicated data on another column in the table so it will have a result like this :

  id |  Name   | code 
   -------------------
  1  |  John   |  001  
  1  |  John   |  AC2  
  2  |  Anna   |  002  
  2  |  Anna   |  AH5  
  3  |  Brad   |  003  
  3  |  Brad   |  BB1

CodePudding user response：

You need to use a UNION or UNION ALL:

SELECT
    id,
    Name,
    code1 As code
FROM
    YourTable

UNION

SELECT
    id,
    Name,
    code2 As code
FROM
    YourTable

ORDER BY
    id,
    Name,
    code

UNION (Transact-SQL) - SQL Server | Microsoft Docs

NB: As per Aaron's comment below, UNION will remove duplicate rows, whereas UNION ALL will include them. If you have the same value in code1 and code2 on a single source row, UNION will produce a single output row, whereas UNION ALL will produce two identical output rows. It's not clear from your question which behaviour you would want.

CodePudding user response：

You can do this with only a single scan of the source table. Simply unpivot using CROSS APPLY (VALUES

SELECT
  t.id,
  t.Name,
  v.code
FROM YourTable t
CROSS APPLY (VALUES
    (code1),
    (code2)
) v(code)