Applying conditions between columns in sqlserver

Question

Primary    Secondary
-----------------------------------
UST         MUN
Data        NULL
Tele        Data
Sun         Data
RMDS        Data
Data        NULL
Mor         NULL
UST         NULL
Data        Bridge

I want to check if values of "Secondary" is already in "primary" then it should come as "primary" but if it is not in "primary" values it must come under "Secondary" column only.

Here I want output as:

Primary  Secondary
------  -----------
UST      MUN
Data     Bridge
Tele
Sun
RMDS
Mor

possible duplicate of http://stackoverflow.com/questions/11302617/select-distinct-values-from-multiple-columns-in-same-table — Bhavik Patel, Feb 15 '17 at 10:11
i think the answer given is the correct one, did you try that? — Bhavik Patel, Feb 16 '17 at 09:35
I want to check if values of "Secondary" is already in "primary" then it should come as "primary" but if it is not in "primary" values it must come under "Secondary" column only. "i want this out put" — Himani, Feb 16 '17 at 10:05
Primary Secondary ------ ----------- UST MUN Data Bridge Tele Sun RMDS Mor Bridge — Himani, Feb 16 '17 at 10:12
`bridge` is not in `primary`, but in your desired output in question it is under `primary`. ? — Bhavik Patel, Feb 16 '17 at 10:38
Sorry My bad ,Bridge must come under "secondary" as it was not in "primary" list.Is that possible?? how can i get output like this ? — Himani, Feb 16 '17 at 10:42

Serge · Answer 1 · 2017-02-15T10:18:22.403

1

A UNION (rather that UNION ALL) will return all distinct values you require:

SELECT Primary
FROM MyTable

UNION

SELECT Secondary
FROM MyTable
WHERE Secondary IS NOT NULL

edited Feb 15 '17 at 10:18

answered Feb 15 '17 at 10:09

Serge

3,986
2
17
37

Thanks ,But Null value is also coming in output, but i do not want this – Himani Feb 15 '17 at 10:14
select * from (select primaryFeed as feed from #feedinfo union select SecondaryFeed from #feedinfo) fedd where feed is not null – Himani Feb 15 '17 at 10:16
Hey can you answer my above query ? – Himani Feb 16 '17 at 08:41
@Himani, I updated the query and added `WHERE Secondary IS NOT NULL`. Or, is it the case that you want the output in two columns? – Serge Feb 16 '17 at 12:22
This is fine, but if i want to add one more column in output as 'type' and it should output by checking ( 2 conditions , 1) if 2 col value is in 1st col then 'type' is 'primary' but 2) if 2nd col is not in 1st col then 'type' is secondary – Himani Feb 16 '17 at 12:52
Primary type ------ ----------- UST primary Data primary Tele primary Sun primary RMDS primary Mor primary MUN Secondary Bridge Secondary here MUN and Bridge were not in 1st col so they should come as secondary i tried this: – Himani Feb 16 '17 at 12:55
select pri as feed,'type' = (case when sec not in (select distinct pri from #feedinfo ) then 'sec' else 'pri' end) from #feedinfo union select sec ,'type' = (case when sec in (select distinct pri from #feedinfo ) then 'pri' else null end) from #feedinfo – Himani Feb 16 '17 at 12:56

score 0 · Answer 2 · answered Feb 16 '17 at 11:34

Both rows in one output is not possible since you dont have the combining factor. How will the db know that UST - MUN should come in same row and not UST - Bridge .

What you can get it in a two different outputs and build your programming logic around it.

For first row you can do :

SELECT * FROM 
(
SELECT t1.`primary` FROM tableName t1 
UNION
SELECT t1.`primary` FROM tableName t1 
INNER JOIN tableName t2 ON t1.primary = t2.`secondry`
) AS test1

And for second you can do :

SELECT DISTINCT t2.`secondry` FROM tableName t1 
RIGHT JOIN tableName t2 ON t1.primary = t2.`secondry` 
WHERE  t1.primary IS  NULL AND t2.secondry IS NOT NULL

Applying conditions between columns in sqlserver

2 Answers2