CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Ashish Kumar
Ashish Kumar

Reputation: 5

Splitting out rows with multiple values in SQL

I am trying to split out few values from a database.

Sample data

There are two columns, Test and Test_Parameter. I want to extract those rows which have different "test_parameter" values for same "Test" value. For example, in my screenshot, I want "Grade:" to be selected as it has different values of Test_Parameter. I know it has a very simple solution but I am not able to figure it out.

Upvotes: 0

Views: 73

Answers (4)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 26078

I like Gordon's answer better, but a maybe simple you understand albeit I suspect slower method would be:

SELECT test 
FROM (
    SELECT test,
        COUNT(DISTINCT(test_parameter) AS c
    FROM table1
    GROUP BY 1
)
WHERE c > 1

the above is if you really don't want the counts in the results, otherwise the having clause makes its simpler:

SELECT test,
    COUNT(DISTINCT(test_parameter) AS c
FROM table1
GROUP BY 1
HAVING c > 1

Upvotes: 0

MKP
MKP

Reputation: 196

Try any of below to remove colon and compare :

    SELECT DISTINCT test FROM table1 WHERE test NOT LIKE test_parameter||'%'

    SELECT DISTINCT test FROM table1 WHERE SUBSTR(test,1,INSTR(test,':')-1) <> test_parameter

Upvotes: 0

Mike Walton
Mike Walton

Reputation: 7369

I might be confused by the ask, but wouldn't this work?

SELECT *
FROM t
WHERE test <> test_parameter

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270873

You can use aggregation and having:

select test
from t
group by test
having min(test_parameter) <> max(test_parameter);

Upvotes: 1

Related Questions