SQL在单独的行上选择每个选定列的值 [英] SQL select values of each selected columns on separate rows

问题描述

我有一个包含数百行和数十个W列的表:

I have a table with hundreds of rows and tens of W columns:

Column1 | Column2_W | Column3_W | ColumnX_W
  123   |     A     |     B     |    x
  223   |     A     |    NULL   |  NULL

如何选择它，以便输出为:

How can i select it so that the output would be:

Column1 | W 
  123   | A
  123   | B
  123   | x
  223   | A

我很清楚我正在使用可怕的数据库设计".不幸的是我不能改变它.这个问题实际上是我今天遇到的一个更大问题的一部分.我明天将尝试给定的想法

I am well aware that i am working with a terrible DB "design". Unfortunately i can't change it. This question is actually part of a larger problem i was handed today. I will try the given ideas tomorrow

推荐答案

看看本文:不幸的是，您将无法使用任何使用的解决方案来键入列名称.这是在SQL Server中使用UNPIVOT的示例...

Unforunately, you're going to be stuck hand typing the column names with any of the solutions you use. Here's a sample using UNPIVOT in SQL Server...

SELECT Column1, W
FROM YourTable
UNPIVOT (W for Column1 in (Column2_W, Column3_W /* and so on */)) AS W

这篇关于SQL在单独的行上选择每个选定列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！