CODEWITHSUNDEEP
sql-servert-sql
Faye D.
Faye D.

Reputation: 732

Find different values in a column grouped by another column

Suppose a table like the extract below:

ITEID SUBSTITUTECODE SUBSTITUTEDESCR MASTERQTY SUBSTQTY SUBSTITEID COLORCODE SIZEPOS ISEAN13
4454 3468336065181 NULL 1 1 NULL ΜΑΥ 7 0
4454 3468336065754 NULL 1 1 NULL ΜΑΥ 6 0
4456 3468336053201 NULL 1 1 NULL ΜΑΥ Λ 7 0
4456 3468336053287 NULL 1 1 NULL ΜΑΥ Λ 8 0
4456 3468336057391 NULL 1 1 NULL ΜΑΥ Λ 9 0
4456 3468336058664 NULL 1 1 NULL ΜΑΥ Λ 6 0
4462 3468336073957 NULL 1 1 NULL ΜΑΥ 1 0
4462 3468336073995 NULL 1 1 NULL ΜΑΥ 3 0
4462 3468336074152 NULL 1 1 NULL ΜΑΥ 2 0
4462 3468336074381 NULL 1 1 NULL ΜΑΥ 13 0
4462 3468336074503 NULL 1 1 NULL ΜΑΥ 11 0
4462 3468336074664 NULL 1 1 NULL ΜΑΥ 9 0
4462 3468336075012 NULL 1 1 NULL ΜΑΥ 5 0
4464 3468336152294 NULL 1 1 NULL ΜΑΥ ΠΡΑ 9 0
4464 3468336155493 NULL 1 1 NULL ΜΑΥ ΠΡΑ 7 0
4464 3468336155578 NULL 1 1 NULL ΜΑΥ ΠΡΑ 8 0
4469 3468336206249 NULL 1 1 NULL ΜΠΛΕ 2 0
4469 3468336206300 NULL 1 1 NULL ΜΠΛΕ 9 0
4469 3468336206546 NULL 1 1 NULL ΜΠΛΕ 11 0
4469 3468336206676 NULL 1 1 NULL ΜΠΛΕ 5 0
4511 3468335382395 NULL 1 1 NULL ΚΟΚ 3 0
4511 3468335382401 NULL 1 1 NULL ΚΟΚ 4 0
4511 3468335382418 NULL 1 1 NULL ΚΟΚ 5 0
4511 3468335382425 NULL 1 1 NULL ΚΟΚ 6 0
4511 3468335382432 NULL 1 1 NULL ΚΟΚ 7 0

Using a SQL Server query, I would like to make sure/check at a glance that all rows grouped by ITEID have the same value in the field COLORCODE... One way I could think of (in MySQL though, which I'm familiar with) would be to use a GROUP_CONCAT(COLORCODE) ... GROUP BY (ITEID) which unfortunately doesn't work in the SQL Server.

Can someone familiar with SQL Server craft the appropriate for me please? Thank you in advance.

Edit:

An expected result would be something like this:

ITEID CONCATENATED_COLORCODE
4454 ΜΑΥ
4456 ΜΑΥ Λ
4462 ΜΑΥ
4464 ΜΑΥ ΠΡΑ
4469 ΜΠΛΕ
4511 ΚΟΚ
4511 ΚΟΚ
BUT -> 5000 NAVY, something_else

So at a glance, I would notice that for ITEID 5000, the person who inserted the data, made a mistake...

Upvotes: -1

Views: 75

Answers (1)

GrahamH
GrahamH

Reputation: 360

maybe? (please do the set up work yourself next time)

DROP TABLE IF EXISTS YT

CREATE TABLE YT(
   ITEID           INT  NOT NULL
  ,COLORCODE       NVARCHAR(7)
 
);
INSERT INTO YT(ITEID,COLORCODE) VALUES
 (4454,N'ΜΑΥ'),(4454,N'ΜΑΥ'),(4456,N'ΜΑΥ Λ'),(4456,N'ΜΑΥ Λ'),(4456,N'ΜΑΥ Λ')
,(4456,N'ΜΑΥ Λ'),(4462,N'ΜΑΥ'),(4462,N'ΜΑΥ'),(4462,N'ΜΑΥ'),(4462,N'ΜΑΥ')
,(4462,N'ΜΑΥ'),(4462,N'ΜΑΥ'),(4462,N'ΜΑΥ'),(4464,N'ΜΑΥ ΠΡΑ'),(4464,N'ΜΑΥ ΠΡΑ')
,(4464,N'ΜΑΥ ΠΡΑ'),(4469,N'ΜΠΛΕ'),(4469,N'ΜΠΛΕ'),(4469,N'ΜΠΛΕ'),(4469,N'ΜΠΛΕ')
,(4511,N'ΚΟΚ'),(4511,N'ΚΟΚ'),(4511,N'ΚΟΚ'),(4511,N'ΚΟΚ'),(4511,N'ΚΟΚ')
-- ADDED DATA TO DEMONSTRATE YOUR QUESTION
,(4511,N'ΚΟΚ'),(4511,N'BAL'),(4511,N'NIK'),(4511,N'DIK'),(4511,N'ΚIΚ')
;

SELECT   ITEID, COUNT(DISTINCT COLORCODE) AS cnt
FROM      YT
GROUP BY ITEID
HAVING COUNT(DISTINCT COLORCODE) > 1

Upvotes: 2

Related Questions