Select the same row twice with different columns values without using union

Question

1.00/5 (1 vote)

See more:

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

SQL

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

Posted 4-Jun-18 12:53pm

Samira Radwan

Updated 4-Jun-18 14:39pm

Add a Solution

Comments

Mohibur Rashid 4-Jun-18 19:51pm

Why do you hate UNION?

If you are having problem with getting both item, use
UNION ALL

Samira Radwan 4-Jun-18 20:06pm

it's a requirement not to use UNION or UNION ALL

Mohibur Rashid 4-Jun-18 20:18pm

It's a homework!

Samira Radwan 4-Jun-18 20:23pm

no, my boss doesn't like UNION !!
said need to be done in other way, which i don't really know

Mohibur Rashid 4-Jun-18 20:25pm

that's odd! try UNPIVOT, I am not sure if it would help https://stackoverflow.com/questions/18026236/sql-server-columns-to-rows

Richard Deeming 6-Jun-18 13:06pm

Then perhaps your boss should explain what he thinks the solution would be, and why he doesn't like UNION.

Sounds like a bad case of "cargo-cult" programming - he once skimmed through an article somewhere which had a specific scenario where UNION caused problems, and all he took away from it was the idea that "UNION is bad", without anything to support that opinion.

Or maybe he was referring to Trade Unions[^], which is a completely different thing. :)

Samira Radwan 8-Jun-18 11:09am

LOL :)
Sometimes it's better not to ask!! ;)
Also, looking at the bright side I have learnt new approach thanks to @Mohibur :)
Thank you!

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Mohibur Rashid · Accepted Answer · 2018-06-04T14:41:00

A sample test for you with UNPIVOT, ran in sqlfiddle.com

SQL

-- 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)
;

SQL

-- query
SELECT
    id
  , choice
FROM geek
UNPIVOT (
  choice
  FOR value in (choice1, choice2)
) unpiv

WHERE choice > 0
;

SQL

--result
id	choice
1	10
1	20
2	30
3	40

sqlfiddle link[^]