使用不同的列值选择相同的行两次而不使用union [英] Select the same row twice with different columns values without using union
问题描述
大家好,
如果我的桌子看起来像这样:
(ID,fName,lName,choice1, choice2)
如果我选择* myTable,其中Id = 1 它应返回1行,有2个选项
我想从表格中选择同一行两次,其中一个选项每次如下:
1,John,Doe,choice1
1,John,Doe,choice2
任何想法如何实现这个没有使用联盟?
任何帮助将不胜感激
我尝试过的事情:
我尝试使用工会应该工作
Hi All,
If I'm having a table looks like this:
(ID,fName,lName,choice1,choice2)
If I select * myTable where Id=1 it should return 1 row with 2 choices
I would like to select from the table same row twice with one of the choices everytime as follow:
1,John , Doe, choice1
1,John, Doe, choice2
Any ideas how to implement this without using union?
Any help will be appreciated
What I have tried:
I tried using union which should work
Select Id,fName,lName,choice1
from myTable
WHERE choice1>0
UNION
Select Id,fName,lName,choice2
from myTable
WHERE choice2>0
--I'm looking for a solution that doesn't use union
推荐答案
使用UNPIVOT进行样本测试,在sqlfiddle中运行。 com
A sample test for you with UNPIVOT, ran in sqlfiddle.com
-- SCHEMA
CREATE TABLE geek
(id int, [choice1] int, [choice2] int)
;
INSERT INTO geek
([id], [choice1], [choice2])
VALUES
(1, 10, 20),
(2, 30, 0),
(3, 0, 40)
;
-- query
SELECT
id
, choice
FROM geek
UNPIVOT (
choice
FOR value in (choice1, choice2)
) unpiv
WHERE choice > 0
;
--result
id choice
1 10
1 20
2 30
3 40
sqlfiddle链接 [ ^ ]
这篇关于使用不同的列值选择相同的行两次而不使用union的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!