0

I have searched and cannot find the answers to what I'm trying to achieve. Let's say for example I have this data. 

|SN    | column1   | column2  | column3 | column4 | column5 | column6 |
+------+-----------+----------+---------+---------+---------+---------+
|1     | orange    | banana   | apple   | grapes  | null    |    555  |
|2     | banana    | grapes   | null    | null    | null    |    555  |
|3     | grapes    | orange   | banana  | null    | null    |    555  |
|4     | orange    | banana   | grapes  | null    | null    |    555  |

I want a search where all rows have duplicates in any column and where column6 is 555. The results would show grapes and banana regardless of SN column. because 555 exist and grapes exist in every row. see sample results below:


|SN | column1   | column2  | column3  | column4  | column5 | column6 |
+---+-----------+----------+----------+----------+---------+---------+
|1  | orange    |**banana**| apple    |**grapes**| null    |    555  |
|2  |**banana** |**grapes**| null     | null     | null    |    555  |
|3  |**grapes** | orange   |**banana**| null     | null    |    555  |
|4  | orange    |**banana**|**grapes**| null     | null    |    555  |

I have tried to use the count, sample code below but the results is blank. 



select column1, column2, column3, column4, column5, COUNT(*)
From Sheet Group By 
column1, column2, column3, column4, column5
Having 
Count(*) > 1

The output should show me all columns with same value which banana/grapes and 555

Please help!

vorakly
  • 13
  • 2

1 Answers1

0

You can unpivot the table first and the do a group by to find the value you are looking for.

Couple of things you need to know to understand the answer a lil better.

  1. What in UNPIVOT-ing of data. Read about that here on MSDN official documentation.

  2. how can we UNPIVOT data using CROSS APPLY. See this link for example

See query below and a live demo 

; with upview as
(
select yt.sn, ytc.col, column6
from yourtable yt
cross apply
( values (column1),(column2),(column3),(column4),(column5)) ytc(col)
)
,
gettheSN as 
(
select col  
from upview
where column6=555
group by col
having count(sn)=
(select count(1) from yourtable)
)
select * from gettheSN

Now if you need to unpivot more columns say column7 and column8 after column6 which we are not unpivoting then your cross apply part will look like

cross apply
( values (column1),(column2),(column3),(column4),(column5), (column7),(column8)) ytc(col)
)
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
  • Hi! this code works in your demo but how come it doesn't work in my MSSQL express? and the results are correct. – vorakly Dec 14 '18 at 17:15
  • That's strange, I copied the entire code you have on the live demo and place it into MSSQL and able to run off your code. I noticed the difference between my table are the Data Type, I changed my table to match your data type but my results are zero. – vorakly Dec 15 '18 at 00:17
  • When I add more columns and rows to the table created with your code in the demo, the query no longer works. Any thoughts? @DhruvJoshi – vorakly Dec 17 '18 at 18:28
  • @vorakly I guess there are couple of things you need to know better to understand the answer. I am editing my answer – DhruvJoshi Dec 17 '18 at 18:39
  • thank you for those link, I will have to read it several more times for it to sink in. But i can reproduce the error using your live demo. If I change any of the values in column6 then there are NO results. But in my large table there will be different numbers in column6. I can remove the where column6=555 but I need that to sort. @DhruvJoshi Please advise – vorakly Dec 17 '18 at 22:05