OM Asphyxiate OM Asphyxiate - 5 months ago 8
SQL Question

Check for same character value in column

I'm trying to verify when a column has all the same values for the same group. Here is a sample of my table data:

Sample Data

So using this data, for example. I want to check to see if all values of

Status
is the same for every row with the same
TPID
. So
TPID
60210 should result with True since both items have a
Status
of A. However,
TPID
60061 should result in false since two of the
Line_Item
show A and the rest P.

I intend to update a different table using this information, setting its
status
using a CASE statement. But I'm at a loss how to check against this column to find the values I desire.

Answer

As I understand, May be you want something like this:-

;WITH CTE_Count
AS
(
   SELECT TPID, COUNT(DISTINCT Status) CNT
   FROM TableName
   GROUP BY TPID
 )

 UPDATE AnotherTableName
 SET ColumnName = (
   CASE WHEN CTE_Count.CNT = 1 -- all row has same status
      THEN SomeValue
    ELSE SomeOtherValue END
)
FROM AnotherTableName
INNER JOIN CTE_Count ON ...

Hope it helps & let me know it i miss anything.