CODEWITHSUNDEEP
sqlsql-server
Michael Loose
Michael Loose

Reputation: 33

Need to find out if all columns in a SQL Server table have the same value

I have the task to find out if all columns in a SQL Server table have exact the same value. The table content is created by a stored procedure and can vary in the number of columns. The first column is an ID, the second and the following columns must be compared if the all columns have exact the same value.

At the moment I do not have a clue how to achieve this.

The best solution would be to display only the rows, which have different values in one or multiple columns except the first column with ID.

Thank you so much for your help!!

--> Edit: The table looks this:

ID   Instance1     Instance2     Instance3      Instance4      Instance5
=====================================================
A    1                    1                     1                     1                      1
B    1                    1                     0                     1                      1
C    55                  55                  55                   55                    55
D    Driver            Driver            Driver             Co-driver         Driver
E     90                 0                     90                  0                       50   
F     On                 On                  On                 On                    On

The result should look like this, only the rows with one or multiple different column values should be display. 

ID   Instance1     Instance2     Instance3      Instance4      Instance5
=====================================================
B    1                    1                     0                     1                      1
D    Driver            Driver            Driver             Co-driver         Driver
E     90                 0                     90                  0                       50

My table has more than 1000 rows and 40 columns

Upvotes: 3

Views: 2166

Answers (2)

Alvaro Parra
Alvaro Parra

Reputation: 805

you can achieve this by using row_number() Try the following code 

With c as(
Select    id
,field_1
,field_2
,field_3
,field_n
,row_number() over(partition by field_1,field_2,field_3,field_n order by id asc) as rn
From Table
)
Select * 
From c 
Where rn = 1

row_number with partition is going to show you if the field is repeated by assigning a number to a row based on field_1,field_2,field_3,field_n, for example if you have 2 rows with same field values the inner query is going to show you 

rn field_1 field_2 field_3 field_n id 
1    x       y       z        a    5 
2    x       y       z        a    9

After that on the outer part of the query pick rn = 1 and you are going to obtain a query without repetitions based on fields.

Also if you want to delete repeated numbers from your table you can apply

  With c as(
    Select    id
    ,field_1
    ,field_2
    ,field_3
    ,field_n
    ,row_number() over(partition by field_1,field_2,field_3,field_n order by id asc) as rn
    From Table
    )
    delete
    From c 
    Where rn > 1

Upvotes: 1

GMB
GMB

Reputation: 222652

The best solution would be to display only the rows, which have different values in one or multiple columns except the first column with ID.

You may be looking for a the following simple query, whose WHERE clause filters out rows where all fields have the same value (I assumed 5 fields - id not included).

SELECT *
FROM mytable t
WHERE NOT (
        field1 = field2
    AND field1 = field3
    AND field1 = field4
    AND field1 = field5
);

Upvotes: 0

Related Questions