SQL SERVER 2008 Pivot

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 26, 2010 at 6:46 am

  #1108624

  Why do you still include the local temporary table #sipDimensionPos in your solution? If you were creating it as a filtered subset of the table from which it is derived (i.e. with a WHERE clause), then it would make sense, but there's no filter...

  Does it run?

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• datelligence

  Say Hey Kid

  Points: 672

  More actions

  January 26, 2010 at 7:01 am

  #1108639

  Ok I´m a little bit lost. So the temporary table was created just for say, the problem resolution, something didactic. Your rigth there is no filtering a subset of the table. Again plese check this, I removed the whole bunch of temporary table.

  --===== Create a table variable to store the id_CliPadre

  DECLARE @PivotCliPadres VARCHAR(1000)

  SELECT @PivotCliPadres = COALESCE(

  @PivotCliPadres + ',[' + cast(id_CliPadre as varchar) + ']',

  '[' + cast(id_CliPadre as varchar)+ ']')

  FROM (

  SELECT id_CliPadre

  FROM dbo.sipDimensionPos

  GROUP BY id_CliPadre

  ) d

  ORDER BY d.id_CliPadre

  -- check

  --SELECT @PivotCliPadres

  --===== Execute the pivot with a table variable

  DECLARE @PivotTableSQL NVARCHAR(MAX)

  SET @PivotTableSQL = N'

  SELECT *

  FROM (select codigo_item, item, round(net_price,1) as net_price, id_CliPadre, id_stock from dbo.sipFTdataStage INNER JOIN

  dbo.sipDimensionPos ON dbo.sipFTdataStage.id_cliente = dbo.sipDimensionPos.id_cliente) tabla

  pivot (

  sum(net_price) for id_CliPadre IN (

  ' + @PivotCliPadres + '

  )

  ) pivotable

  ,

  print (@PivotTableSQL)

  EXECUTE(@PivotTableSQL)'

• datelligence

  Say Hey Kid

  Points: 672

  More actions

  January 26, 2010 at 7:05 am

  #1108641

  However once executed I receive this message:

  "Command(s) completed successfully."

  How do I query the pivoted data?

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 26, 2010 at 7:12 am

  #1108648

  You have a closing quotation mark in the wrong place

  ,

  print (@PivotTableSQL)

  EXECUTE(@PivotTableSQL)' -- <-- closing quotation mark in the wrong place

  which means that the PRINT statement and the EXECUTE statement are appended to the string @PivotTableSQL, instead of being executed. Put it back in the right place, which you can find from an earlier post where you successfully ran the dynamic sql query.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• datelligence

  Say Hey Kid

  Points: 672

  More actions

  January 26, 2010 at 7:34 am

  #1108667

  Yes, you´re great, it works, I get a pivoted datagrid. Thanks a lot. How do I award you. I´m grateful with you!!

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  January 26, 2010 at 7:41 am

  #1108681

  A squash from your nice terracotta tiled roof 😉

  Top work, Jorge. It's good to work with a poster who's prepared to put in some effort, as you have always.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• datelligence

  Say Hey Kid

  Points: 672

  More actions

  January 26, 2010 at 7:46 am

  #1108685

  Thank´s Chris, really learned and enjoyed. Thanks for asking me to give a little bit and not just ask for the solution.