how to add intermediate columns in PIVOT tabe????

• kavinithi

  Old Hand

  Points: 397

  More actions

  August 29, 2011 at 12:06 am

  #247146

  hI ALL...

  I'm keep getting PIVOT table problems 🙂

  HAving a PIVOT table(THE FINAL RESULT) AS

  BUDGET ACTIVITIES JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC

  BUDGET 2010 Glass Distribut NULL 0 200 100 150 200 100 150 200 100 200 100

  BUDGET 2010 BUMI NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL NULL

  BUDGET 2010 Verre soluts NULL 150 200 100 150 200 100 150 200 100 200 100

  BUDGET 2010 Chantiers NULL 150 200 100 150 200 100 150 200 100 200 100

  BUDGET 2010 Spécialité NULL 150 200 100 150 200 100 150 200 100 200 100

  BUDGET 2010 Autres activit NULL 150 NULL NULL NULL 200 100 150 200 100 200 100

  BUDGET 2010 Total NULL 600 800 400 600 1000 500 750 1000 500 1000 500

  here i've to add an intermediate col for edit flag in this PIVOT table like

  BUDGET ACTIVITIES JAN JAN-EDIT FEB FEB-EDIT MAR MAR-EDIT APR APR-EDIT MAY MAY-EDIT JUN JUN-EDIT JUL JUL-EDIT AUG AUG-EDIT SEP SEP-EDIT OCT OCT-EDIT NOV NOV-EDIT DEC DEC-EDIT

  PLZ HELP ME REGARDING THIS.

• R.P.Rozema

  SSChampion

  Points: 12305

  More actions

  August 29, 2011 at 2:28 am

  #1374399

  Have a look at Jeff Moden's articles on cross-tab[/url]. These are a (better) alternative to the use of the pivot command. Among other things, cross-tab allows for adding additional columns like you require.

  ---

  
  Posting Data Etiquette - Jeff Moden[/url]
  Posting Performance Based Questions - Gail Shaw[/url]
  Hidden RBAR - Jeff Moden[/url]
  Cross Tabs and Pivots - Jeff Moden[/url]
  Catch-all queries - Gail Shaw[/url]
  

  ---

  
  If you don't have time to do it right, when will you have time to do it over?

• kavinithi

  Old Hand

  Points: 397

  More actions

  August 29, 2011 at 3:56 am

  #1374420

  THANK U Rozema:-)

• nithiyanandam-447806

  Ten Centuries

  Points: 1197

  More actions

  August 29, 2011 at 9:31 pm

  #1374803

  Hi,

  1. Add 12 columns to temp table (JanFlag .....DecFlag)

  Column Datatype should be tinyint. We need to insert the following values.

  0 - not editable

  1 - editable

  2 - month first half editable (1 to 14)

  3 - month second half editable (15 to enddate)

  2. update all flags values to 0(Zero).

  3. Then apply your logic...

  declare @month tinyint

  set @month = datepart(month, getdate())

  select @month

  -- for your last requirement

  if (@month = 1)

  update #t set JanFlag = 3, FebFlag = 2

  where left(budget,6) = 'PNVRES'

  Else if (@month = 2)

  update #t set FebFlag = 3, MarFlag = 2

  where left(budget,6) = 'PNVRES'

  Else if (@month = 3)

  update #t set MarFlag = 3, AprFlag = 2

  where left(budget,6) = 'PNVRES'

  ......

  ....

  ...

  ..

  Else if (@month = 11)

  update #t set NovFlag = 3, DecFlag = 2

  where left(budget,6) = 'PNVRES'

  Else if (@month = 12)

  update #t set DecFlag = 3, JanFlag = 2

  where left(budget,6) = 'PNVRES'

  Thanks,

  Nithi