Help with query

• vmon

  SSChasing Mays

  Points: 624

  More actions

  June 21, 2011 at 7:58 am

  #238314

  I am having trouble getting a query worked out. I have a table that has 2 columns, item and code. An item may have 1 or more codes. A new code assigned to an item would add a row for that item. I need to determine the commonality of codes used by items. In other words how many items have the same codes assigned to them so that I can identify the unique sets of code usage. Here is an example of what the data looks like. The code group column would be a counter that would identify a unique set of codes.

  Any help would be awesome

  Thanks in advance.

  vmon

  Item1 CodeA

  Item1 CodeB

  Item1 CodeC

  Item2 CodeA

  Item2 CodeB

  Item3 CodeC

  Item4 CodeA

  Item4 CodeB

  Item4 CodeC

  Item5 CodeA

  Item5 CodeB

  Item6 CodeA

  Item6 CodeB

  Item6 CodeC

  CodeGroup1, Item1, CodeA, CodeB, CodeC

  CodeGroup1, Item4, CodeA, CodeB, CodeC

  CodeGroup1, Item6, CodeA, CodeB, CodeC

  CodeGroup2, Item2, CodeA, CodeB

  CodeGroup3, Item5, CodeA, CodeB

  CodeGroup4, Item3, CodeC

• The Dixie Flatline

  SSC Guru

  Points: 53354

  More actions

  June 21, 2011 at 8:26 am

  #1341873

  declare @sample table (Item varchar(20), Code varchar(20))

  insert into @sample

  Select 'Item1' ,'CodeA' union all

  Select 'Item1' ,'CodeB' union all

  Select 'Item1' ,'CodeC' union all

  Select 'Item2' ,'CodeA' union all

  Select 'Item2' ,'CodeB' union all

  Select 'Item3' ,'CodeC' union all

  Select 'Item4' ,'CodeA' union all

  Select 'Item4' ,'CodeB' union all

  Select 'Item4' ,'CodeC' union all

  Select 'Item5' ,'CodeA' union all

  Select 'Item5' ,'CodeB' union all

  Select 'Item6' ,'CodeA' union all

  Select 'Item6' ,'CodeB' union all

  Select 'Item6' ,'CodeC'

  ;with cte as (select item,code,row_number() over (partition by item order by code) as ColNo from @sample)

  ,cte2 as (select item

  ,MAX(case when colNo = 1 then code else '' end) as Code1

  ,MAX(case when colNo = 2 then code else '' end) as Code2

  ,MAX(case when colNo = 3 then code else '' end) as Code3

  ,MAX(case when colNo = 4 then code else '' end) as Code4

  ,MAX(case when colNo = 5 then code else '' end) as Code5

  ,MAX(case when colNo = 6 then code else '' end) as Code6

  from cte

  GROUP by item)

  select Dense_Rank() over(order by code1,code2,code3,code4,code5,code6) as CodeGroup, *

  from cte2

  __________________________________________________

  Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
  Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills