68800

How do I stack the first two columns of a table into a single column?

Question:

I'd like to stack two columns of a table into a single column. So for example, if the source table is: | Id | Code_1 | Code_2 | Other | |----|---------|---------|-------| | 1 | Value_1 | Value_2 | Row_1 | | 2 | Value_3 | (null) | Row_2 | | 3 | (null) | Value_4 | Row_3 | | 4 | (null) | (null) | Row_4 | | 5 | Value_5 | (null) | Row_5 |

Then the target table should be: | Id | Code | Other | |----|---------|-------| | 1 | Value_1 | Row_1 | | 2 | Value_2 | Row_1 | | 3 | Value_3 | Row_2 | | 4 | Value_4 | Row_3 | | 5 | Value_5 | Row_5 |

Notice that the Other column just duplicates its values, but only when there's a non-null value in Code_1 and Code_2.

I tried to use a PIVOT statement cos I'm essentially trying to (almost) stack transposes of each row, but I can't figure out how to orchestrate it properly.

I have the DDL is below:

CREATE TABLE Source ( Id INT IDENTITY(1, 1) PRIMARY KEY, Code_1 VARCHAR(10), Code_2 VARCHAR(10), Other VARCHAR(10) ); INSERT INTO Source VALUES ('Value_1', 'Value_2', 'Row_1'), ('Value_3', NULL, 'Row_2'), (NULL, 'Value_4', 'Row_3'), (NULL, NULL, 'Row_4'), ('Value_5', NULL, 'Row_5');

CREATE TABLE Target ( Id INT IDENTITY(1, 1) PRIMARY KEY, Code VARCHAR(10), Other VARCHAR(10) ); INSERT INTO Target VALUES ('Value_1', 'Row_1'), ('Value_2', 'Row_1'), ('Value_3', 'Row_2'), ('Value_4', 'Row_3'), ('Value_5', 'Row_5')

Answer1:

You need unpivot:

SELECT Id, Code, Other FROM Source UNPIVOT (Code FOR code_ IN (code_1, code_2) ) AS unpvt;

See the <a href="http://sqlfiddle.com/#!6/e28b4/7" rel="nofollow">fiddle here</a>.

Recommend

  • How to get list of elements by partial class name?
  • Dynamically build select statement in Oracle 12c
  • Connect to Microsoft Azure Machine Learning Studio Api with ruby instead of python use net/http gem
  • How to draw ellipsoid with plotly
  • Function to create Reuleaux Polygons on an HTML 5 canvas
  • Drawing a circle with triangles WebGL
  • How to hide an alias column in mysql EDITED
  • How share x axis of two subplots after they are created?
  • How to draw two arc path without connect line?
  • Using result of first select statement into another and store result into another table
  • Combining multiple atom feeds into one
  • Application failed codesign verification. [duplicate]
  • How do I rotate an arkit 4x4 matrix around Y using Apple's SIMD library?
  • How to plot a filled circle?
  • Pretty good heuristic evaluation rules for big TicTacToe 5x5 board
  • How do I call the Pie function?
  • Find root of a function in a given interval
  • Parsing complicated Json Array in C# [duplicate]
  • Calculate the point of intersection of circle and line through the center
  • How to do a sum in python
  • MATLAB Heart Curve
  • how to do power of complex number in CUBLAS?
  • model.solve() method is not working (“CPLEX DLL not found”) for DOcplex for Python
  • Rails: Converting from MySQL to PostGres breaks Geokit Distance Calculations?
  • Geometry shader doesn't do anything when fed GL_POINTS
  • Retrieve List of Cells in Excel Where Column Equals Something
  • Getting Coordinates of a Point in a circle
  • SQL Server PIVOT Function
  • how to pivot complex dataframe
  • Python sin and cosine giving incorrect values
  • How can I use a custom function within an expression using the eval dataframe method?
  • Build Matrix of Comparisons in SQl Server
  • .NET video play library which allows to change the playback rate?
  • How to 'create temp table as select' in Slick?
  • MySQL Order by column = x, column asc?
  • Shallow update not allowed (git > 1.9)
  • Weird JavaScript statement, what does it mean?
  • jquery mobile loadPage not working
  • How to include full .NET prerequisite for Wix Burn installer
  • Qt: Run a script BEFORE make