List Field Names In Group

Transact-SQL
1

I am trying to seek for field(s) where values are different in each row, based on the group.

In this case, the field My_Group defines the group. I am trying to get the field names, returning: "My_ID" and "My_Letter"

I have seen this done before when comparing two different tables using EXCEPT.

DROP TABLE IF EXISTS #TEMPTABLE_01

CREATE TABLE #TEMPTABLE_01
(
	  My_ID			TINYINT IDENTITY(1,1)	NOT NULL	
	, My_Letter		VARCHAR(5)				NOT NULL
	, My_Group		VARCHAR(5)				NOT NULL
	, My_Table		VARCHAR(5)				NOT NULL
)

INSERT INTO #TEMPTABLE_01(My_Letter, My_Group, My_Table)
OUTPUT INSERTED.*		--	<---------------
SELECT 'A', '1', 'ER'
UNION ALL
SELECT 'B', '1', 'ER'
UNION ALL
SELECT 'C', '2', 'ER'
UNION ALL
SELECT 'C', '2', 'ER'
2

Please post the final result you want and explain why ?

3
My_Group FieldName_Changes
1 [My_Letter][My_ID]
2 [My_ID]

Looking for fields in each group that has more than 1 different value.

4

That just does not make any sense to me. How could group 1 have MyId changed when it is an identity column?

6

Post proper data please. We dont have access to your thought process :grin:

7

I am hoping someone could share the logic to detect fields where row value changes (on GROUP BY) REGARDLESS even if it is an IDENTITY FIELD.

8

If someone else has a cleaner way of doing this, please let me know. Otherwise, this is what I am looking for.

SELECT DISTINCT
	  My_Group
	, CONCAT
		(
		  'Fields: '
		, IIF((SELECT COUNT(DISTINCT My_Letter) FROM #TEMPTABLE_01 WHERE My_Group = T1.My_Group GROUP BY My_Group) > 1, '[My_Letter]',	'')
		, IIF((SELECT COUNT(DISTINCT My_ID)		FROM #TEMPTABLE_01 WHERE My_Group = T1.My_Group GROUP BY My_Group) > 1, '[My_ID]',		'')
		)		AS FieldName_Changes
FROM #TEMPTABLE_01 T1
9

hi BabyAqua

I tried to do this

hope it helps :slight_smile: :slight_smile:

drop create data 
DROP TABLE #TEMPTABLE_01

CREATE TABLE #TEMPTABLE_01
(
	  My_ID			TINYINT IDENTITY(1,1)	NOT NULL	
	, My_Letter		VARCHAR(5)				NOT NULL
	, My_Group		VARCHAR(5)				NOT NULL
	, My_Table		VARCHAR(5)				NOT NULL
)

INSERT INTO #TEMPTABLE_01(My_Letter, My_Group, My_Table)
OUTPUT INSERTED.*		--	<---------------
SELECT 'A', '1', 'ER'
UNION ALL
SELECT 'B', '1', 'ER'
UNION ALL
SELECT 'C', '2', 'ER'
UNION ALL
SELECT 'C', '2', 'ER'
SQL in a different way using case statement 
SELECT DISTINCT b.my_group, 
                CASE 
                  WHEN a.my_letter <> b.my_letter THEN 'My_Letter' 
                END AS 'My_Letter', 
                CASE 
                  WHEN a.my_id <> b.my_id THEN 'My_ID' 
                END AS 'My_ID' 
FROM   #temptable_01 a 
       JOIN #temptable_01 b 
         ON a.my_id = b.my_id + 1
SQL different way using IIF 
SELECT DISTINCT b.my_group, 
                Iif(a.my_letter <> b.my_letter, 'My_Letter', '') AS 'My_Letter', 
                Iif(a.my_id <> b.my_id, 'My_ID', '')             AS 'My_ID' 
FROM   #temptable_01 a 
       JOIN #temptable_01 b 
         ON a.my_id = b.my_id + 1

image

1 Like
10
SQL using IIF 
SELECT DISTINCT b.my_group, 
                Iif(a.my_letter <> b.my_letter, 'My_Letter', '') AS 'My_Letter', 
                Iif(a.my_id <> b.my_id, 'My_ID', '')             AS 'My_ID' 
FROM   #temptable_01 a 
       JOIN #temptable_01 b 
         ON a.my_id = b.my_id + 1
1 Like