GROUP DATASET

• eseosaoregie

  SSCertifiable

  Points: 6889

  More actions

  July 7, 2011 at 8:46 am

  #243936

  I am trying to group a dataset into 2 sets of data. One which has a certain field asccoiated with and then the rest. In my example below I would like to group all the data into RUs which have a Core centre associated with it and others that do not. I have provided data below.

  CREATE TABLE WBU

  (

  RU varchar(8)

  SECTOR varchar(8)

  ENT varchar(10)

  )

  INSERT INTO WBU

  SELECT 'RU303100', 'CORE', 'NVR_ENT2_D' UNION ALL

  SELECT 'RU303100', 'NONCORE', 'NVR_ENT2_E' UNION ALL

  SELECT 'RU303100', 'CORE', 'NVR_ENT4_E' UNION ALL

  SELECT 'RU303200', 'NONCORE', 'NVR_ENT3_D' UNION ALL

  SELECT 'RU303200', 'NONCORE', 'NVR_ENT4_D' UNION ALL

  SELECT 'RU303200', 'CENTER', 'NVR_ENT3_A' UNION ALL

  SELECT 'RU303200', 'NONCORE', 'NVR_ENT5_A' UNION ALL

  SELECT 'RU303300', 'CORE', 'NVR_ENT6_F' UNION ALL

  SELECT 'RU303300', 'CENTER', 'NVR_ENT8_F'

  My output here should be

  RU303100 CORE NVR_ENT2_D

  RU303100 NONCORE NVR_ENT2_E

  RU303100 CORE NVR_ENT4_E

  RU303300 CORE NVR_ENT6_F

  RU303300 CENTER NVR_ENT8_F

  and

  RU303200 NONCORE NVR_ENT3_D

  RU303200 NONCORE NVR_ENT4_D

  RU303200 CENTER NVR_ENT3_A

  RU303200 NONCORE NVR_ENT5_A

• Mark Cowne

  One Orange Chip

  Points: 26865

  More actions

  July 7, 2011 at 9:03 am

  #1349763

  Uncomment /*NOT*/ for the other dataset

  SELECT RU,SECTOR,ENT

  FROM WBU w1

  WHERE /*NOT*/ EXISTS(SELECT * FROM WBU w2 WHERE w2.SECTOR='CORE' AND w2.RU=w1.RU)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• July 7, 2011 at 9:10 am

  This was removed by the editor as SPAM