7

I have a table like:

ID   A0  A1  A2 A3 A4
14   A   B   A  C  A
15   A   A   A  A  A

I need to compare all 5 columns and check if they have same value and retrive the only one that have all same values....in this case the one with ID 15. Is there a way to accomplish that in T-SQL?

12

I'm not familiar with SQL Server's brand of SQL, but it must be something like:

SELECT ID FROM My_Table 
WHERE 
(A0 = A1) AND
(A1 = A2) AND
(A2 = A3) AND
(A3 = A4);

Check out this dbfiddle.uk which shows all the answers in the answer thread.

11

Given the sample data:

CREATE TABLE dbo.Data
(
    ID integer PRIMARY KEY,
    A0 character(1) NULL,
    A1 character(1) NULL,
    A2 character(1) NULL,
    A3 character(1) NULL,
    A4 character(1) NULL
);

INSERT dbo.Data
    (ID, A0, A1, A2, A3, A4)
VALUES
    (14, 'A', 'B', 'A', 'C', 'A'),
    (15, 'A', 'A', 'A', 'A', 'A');

An alternative way to compare all non-ID columns for equality is:

SELECT
    D.*
FROM dbo.Data AS D
WHERE EXISTS
(
    -- All columns except the last one
    SELECT D.A0, D.A1, D.A2, D.A3
    INTERSECT
    -- All columns except the first one
    SELECT D.A1, D.A2, D.A3, D.A4
);

If there are many columns, this may be easier to write than a query with multiple AND clauses (and will often be more compact). In Management Studio, you can drag the Columns node from Object Explorer to a text window to generate a comma-separated column list. Offsetting that list for the second part of the INTERSECT is trivial as well.

The execution plan generated by this query is just as efficient as the multiple AND clause version. Semantically, it is slightly different in that NULL data items will compare equal to each other.

Execution plan

5

I can suggest another solution. You can use COMPUTED COLUMN. Of course this is if you can modify table structure and if you will do that comparison very often.

Inside the COMPUTED COLUMN you can calculate if all columns that you need to compare contains the same data with boolean value. After that you only compare that value in your queries.

Also if you set persistent option the value will be calculated and stored each time you change data. In that case there will be storage overhead.

About COMPUTED COLUMNS you can read for example here

EDIT: As Vérace and Ian Ringrose remarked - if there is an filtered index on COMPUTED COLUMN (in that case it must be persistent) you can benefit performance. Of course there must be enough amount of data.

  • 1
    +1 for this. It could be very useful if columns A0 - A4 aren't/can't be indexed - the query might be much faster. – Vérace Jun 19 '15 at 8:46
  • 10x, @Vérace. I don't think about that kind of application. – Bogdan Bogdanov Jun 19 '15 at 8:47
  • 1
    This is only of benefit if COMPUTED COLUMN is indexed, a filtered index on SQL server works great for this. (Only index rows where the column is 1) – Ian Ringrose Jun 19 '15 at 10:57
  • Yes, @Ian Ringrose, of course. – Bogdan Bogdanov Jun 19 '15 at 11:17
  • I cannot modify the table structure...any other idea? – user68859 Jun 19 '15 at 15:45
3

Another example using cross apply. The idea is to transpose a0, a1, ..., to rows and then check where max and min equals:

select d.id 
from dbo.data as d
cross apply ( values (d.a0),(d.a1),(d.a2),(d.a3),(d.a4) ) as x(a)
group by d.id
having min(a) = max(a);

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.