Sequential data selection

• ssc_san

  SSCommitted

  Points: 1573

  More actions

  June 18, 2014 at 2:25 pm

  #396934

  Hi All,

  how to identify different fields with in a group of records?

  Example:

  create table #test

  (ID int, Text varchar(10))

  insert into #test

  select 1, 'ab'

  union all

  select 1, 'ab'

  union all

  select 2, 'cd'

  union all

  select 2, 'df'

  union all

  select 3, 'ab'

  union all

  select 3, 'cd'

  union all

  select 3, 'df'

  union all

  select 4, 'xy'

  union all

  select 4, 'xy'

  union all

  select 4, 'xy'

  select * from #test

  I want to show additional field as Matched as ID 1 has same Text field on both the records, and for the ID 2 I want to show Unmatched as the Text fields are different but with the same ID.

  Thanks in advance.

• Sean Lange

  SSC Guru

  Points: 286590

  More actions

  June 18, 2014 at 2:55 pm

  #1723006

  Not totally sure what you are looking for but I think this is close.

  select ID

  , case when MIN([text]) = MAX([text]) then 'Matched' else 'Unmatched' end as MyAnswer

  from #test

  group by ID

  _______________________________________________________________

  Need help? Help us help you.

  Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

  Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

  Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
  Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
  Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
  Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/