Filter Pivot Table

• jsshivalik

  SSChasing Mays

  Points: 612

  More actions

  July 9, 2022 at 4:49 pm

  #4061109

  Hi

  I have below query & i want to filter data on 2 parameters

  Create PROC USP_Book

  (

  @ID VARCHAR(500),

  @sid VARCHAR(500)

  )

  AS

  BEGIN

  declare @cols as nvarchar(max)='';

  declare @query as nvarchar(max)='';

  select @cols = @cols + QUOTENAME(SName) + ',' from (Select distinct SName from View_Book) as tmp

  select @cols = substring(@cols,0,len(@cols))

  set @query = 'select Title, ' + @cols + ' from (select Title,Status,SName from tblData) x pivot

  ( Max(Status) for SName in (' + @cols + ')) piv ';

  execute (@query)

  Thanks

• pietlinden

  SSC Guru

  Points: 63428

  More actions

  July 10, 2022 at 2:19 am

  #4061200

  Normally, you do this in the UI... either PowerBI or Excel... unless you don't want to ever see that data.

  you can create a stored procedure and then use that as the source.

• Jeff Moden

  SSC Guru

  Points: 1003863

  More actions

  July 10, 2022 at 6:26 pm

  #4061348

  Ok... that's roughly the same query you've posted two other times.  It's not nice to cross post.  Stick with one thread on something like this.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)