A kinda running total problem??

• vick.ram79

  SSCommitted

  Points: 1637

  More actions

  January 28, 2009 at 10:59 am

  #217968

  Hi Folks,

  There is an issue that I need help with.

  Here is a simplified set of scripts.

  DROP TABLE #mytable

  CREATE TABLE #mytable

  (

  ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

  [Group] VARCHAR(10)

  )

  INSERT INTO #mytable

  ([Group])

  SELECT 'CGroup' UNION ALL

  SELECT 'CGroup' UNION ALL

  SELECT 'CGroup' UNION ALL

  SELECT 'CGroup' UNION ALL

  SELECT 'CGroup' UNION ALL

  SELECT 'ZGroup' UNION ALL

  SELECT 'CGroup' UNION ALL

  SELECT 'CGroup'

  Now I want to select a derived column like below:

  SELECT ID, [GROUP], ColumnC FROM #mytable

  such that:

  1- if all values in [Group] are only 'CGroup' then ColumnC should contain 'Colonised'

  2: If there is even one 'ZGroup' then all values in ColumnC should contain 'Not Colonised'.

  I am currently doing the derived column part in .NET.

  Is there a set-based way to do this?

  Sunil

  How To Post[/url]

• noeld

  SSC Guru

  Points: 96590

  More actions

  January 28, 2009 at 12:47 pm

  #934097

  SELECT t.ID, t.[Group], COALESCE(x.C,'Colonized')

  FROM #mytable t

  OUTER APPLY

  ( SELECT TOP (1) 'Not Colonized' as C

  FROM #mytable t2

  where t2.[Group] = 'ZGroup' ) x

  
  * Noel

• vick.ram79

  SSCommitted

  Points: 1637

  More actions

  January 28, 2009 at 5:47 pm

  #934345

  Awesome.

  Thanks a ton 🙂

  How To Post[/url]