Goruping records with 1 to many relationship

• eseosaoregie

  SSCertifiable

  Points: 6889

  More actions

  May 23, 2011 at 7:10 am

  #238736

  I am trying to extract data from a source table where groups of fields in one table can relate to more than field in the same table. I have created some test data to give an example

  CREATE TABLE #Cartesis_to_hfm

  (

  CartesisRU varchar(8)

  ,CartesisBU varchar(8)

  ,HFMEntity varchar(10)

  ,WBU varchar(3)

  )

  INSERT INTO #Cartesis_to_hfm

  SELECT 'RU303100', 'BU220405', 'NVR_N01_E','4S8' UNION ALL

  SELECT 'RU303100', 'BU220405', 'NVR_N01_E','4S7' UNION ALL

  SELECT 'RU303100', 'BU220405', 'NVR_N01_E','4SB' UNION ALL

  SELECT 'RU303100', 'BU220405', 'NVR_N01_E','5S9' UNION ALL

  SELECT 'RU303102', 'BU220406', 'NVR_N03_E','7RK' UNION ALL

  SELECT 'RU303402', 'BU250406', 'NVR_N10_E','6SB' UNION ALL

  SELECT 'RU305102', 'BU220471', 'NVR_N09_E','6KK'

  Based on this data applied i would like to only extract data where the same combination of :

  CartesisRU,CartesisBU and HFMEntity has more than 1 WBU.

  In the example above I would only like to output to be

  RU303100, BU220405, NVR_N01_E,4S8

  RU303100, BU220405, NVR_N01_E,4S7

  RU303100, BU220405, NVR_N01_E,4SB

  RU303100, BU220405, NVR_N01_E,5S9

  Any ideas?

• John Mitchell-245523

  SSC Guru

  Points: 148786

  More actions

  May 23, 2011 at 7:23 am

  #1328444

  Group by CartesisRU, CartesisBU and HFMEntity and use a HAVING clause.

  John

• Cadavre

  SSC-Forever

  Points: 41690

  More actions

  May 23, 2011 at 7:35 am

  #1328450

  Plenty of different ways to do that. Here's two: -

  --CTE Option

  ;WITH CTE AS (

  SELECT CartesisRU, CartesisBU, HFMEntity, WBU,

  ROW_NUMBER() OVER(partition by CartesisRU, CartesisBU ORDER BY CartesisRU, CartesisBU) AS rn

  FROM #Cartesis_to_hfm)

  SELECT b.CartesisRU, b.CartesisBU,

  a.HFMEntity, a.WBU

  FROM CTE a

  RIGHT OUTER JOIN (SELECT MAX(CartesisRU) AS CartesisRU, MAX(CartesisBU) AS CartesisBU

  FROM CTE

  WHERE rn > 1) b ON a.CartesisRU = b.CartesisRU AND a.CartesisBU = b.CartesisBU

  --Non CTE option

  SELECT a.CartesisRU, a.CartesisBU, b.HFMEntity, b.WBU

  FROM (SELECT CartesisRU, CartesisBU

  FROM #Cartesis_to_hfm

  GROUP BY CartesisRU, CartesisBU

  HAVING COUNT(*) > 1) a

  LEFT OUTER JOIN #Cartesis_to_hfm b ON a.CartesisRU = b.CartesisRU AND a.CartesisBU = b.CartesisBU

  ---

  Forever trying to learn
  My blog - http://www.cadavre.co.uk/
  For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
  For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  ---

• eseosaoregie

  SSCertifiable

  Points: 6889

  More actions

  May 24, 2011 at 1:27 am

  #1328854

  Many thanks. The CTE option worked like a treat.