SQL to see if cells in group match-CodePudding

I have a table where one column has repeating values that I need to group. I then want to see if all of the cells within a different column, but within the same grouping match, or more specifically if they don't match.

This is what I have so far.

SELECT Dashboard_Widget_Id, Position ????? AS Is_Different
FROM toolbox.Dashboard_Widget_Sizes
GROUP BY Dashboard_Widget_Id

CodePudding user response：

Never mind. I think I figured it out.

SELECT Dashboard_Widget_Id, CAST(SIGN(COUNT(t.Dashboard_Widget_Id) - 1) AS BIT) AS Is_Different
FROM (
    SELECT Dashboard_Widget_Id
    FROM toolbox.Dashboard_Widget_Sizes
    GROUP BY Dashboard_Widget_Id, Position
) t
GROUP BY Dashboard_Widget_Id

CodePudding user response：

If you wanted to keep the "detail view", here are two approaches using windowing functions:

DECLARE @t5 TABLE (A INT, B INT, C VARCHAR(100))
INSERT INTO @t5 (A,B,C)
          SELECT 1,1,'1,2,3,4'
UNION ALL SELECT 1,1,'1,2,3,4'

UNION ALL SELECT 2,1,'1,2,3,4'--
UNION ALL SELECT 2,1,'1,2,3,4'---- Same
UNION ALL SELECT 2,1,'1,2,3,4'--

UNION ALL SELECT 3,1,'1,2,7,4'--
UNION ALL SELECT 3,1,'1,2,3,4'---- Different
UNION ALL SELECT 3,1,'1,2,3,4'--

UNION ALL SELECT 4,1,'1,2,3,4'

--"Alternate Partitions are not equal":
SELECT A,B,C,
CASE WHEN COUNT(*) OVER (PARTITION BY A) <> COUNT(*) OVER (PARTITION BY A,C) THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END IsDifferent
FROM @t5 t 

--"DENSE_RANK() OVER Partition > 1":
SELECT 
A,B,C,CASE WHEN MAX(D) OVER (PARTITION BY A) > 1 THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT) END IsDifferent
FROM
(SELECT A,B,C,DENSE_RANK() OVER (PARTITION BY A ORDER BY C) D
FROM @t5 t) a