SQL：需要客户ID，然后是所有不是= 0的列名

I have a table with a customer ID, and then 30+ columns with a 0 or a 1. I'd like to return in the first column the customer ID, and then in the second column the name of the column that <> 0. Ex of what the table has:

我有一个带有客户ID的表,然后是30+列,带有0或1.我想在第一列中返回客户ID,然后在第二列中返回<> 0的列的名称。表的内容如下:

CustomerID, Product1, product2, product3, ...
123456         0          1          1

What I'd like to Return:

我要回报的内容:

123456, Product2
123456. Product3
...

The code I'm using so far...

我到目前为止使用的代码......

2 个解决方案

#1

Sounds like you need to re-design your database to better meet your requirements.

听起来您需要重新设计数据库以更好地满足您的要求。

Given the current scenario should be able to do something along the lines of:

鉴于目前的情况应该能够做到以下几点:

SELECT CustomerID,'Product1' FROM yourtable WHERE product1<>0
UNION 
SELECT CustomerID,'Product2' FROM yourtable WHERE product2<>0
UNION 
SELECT CustomerID,'Product3' FROM yourtable WHERE product3<>0

and so on.

等等。

#2

You would have to unpivot the table, which will be ugly, but can be done simply by using a sufficent amount of unions, like this;

你将不得不对表格进行忽略,这将是丑陋的,但可以通过使用足够数量的联合来完成,就像这样;

SELECT CustomerId, 'Product1' as product FROM mytable WHERE product1 != 0
UNION ALL
SELECT CustomerId, 'Product2' as product FROM mytable WHERE product2 != 0
UNION ALL
SELECT CustomerId, 'Product3' as product FROM mytable WHERE product3 != 0
UNION ALL
   ....
UNION ALL
SELECT CustomerId, 'Product31' as product FROM mytable WHERE product31 != 0
UNION ALL
SELECT CustomerId, 'Product32' as product FROM mytable WHERE product32 != 0;

#1