showing result of query in Pivot table

• profhadoop

  Old Hand

  Points: 329

  More actions

  September 29, 2020 at 10:29 am

  #3791559

  I am trying to develop a pivot table. Please see the attached image for required output. I am unable to design the pivot query for the required solution.

  This is my sql query ::

   

  select 
  b.SchoolName,
  c.KaryakramName,
  a.NikashaAmount
  from TBL_NIKASHA a
  inner join TBL_SCHOOL b on b.SchoolId = a.SchoolId
  inner join TBL_KARYAKRAM c on a.KaryakramId = c.KaryakramId
  WHERE 
  a.NikashaType = 1 and c.karyakramType = 1
  order by a.SchoolId;

  Here, NikashaType & karyakramType come from user and KaryakramName is dynamic.

  I have to show the result of this query in pivot table. What I have done so far::

  declare
  @columns nvarchar(max) = '',
  @sql nvarchar(max) = '';
  
  select
  @columns += QUOTENAME(c.KaryakramName) + ','
  from TBL_NIKASHA a
  inner join TBL_SCHOOL b on b.SchoolId = a.SchoolId
  inner join TBL_KARYAKRAM c on a.KaryakramId = c.KaryakramId
  WHERE 
  a.NikashaType = 1 and c.karyakramType = 1
  
  SET @columns = left(@columns, len(@columns)-1);
  
  set @sql = '
  select * from 
  (
  ---- how to proceed further
  ) t
  pivot
  (
  a.NikashaAmount,
  for c.KaryakramName in ('+ @columns+')
  ) as pivot_table
  ';?

  I don't know how to write pivot table query to get the required solution. Attached is the image of sql query solution and required pivot table solution.

  

  • This topic was modified 4 years, 4 months ago by  profhadoop. Reason: added code

• Jeff Moden

  SSC Guru

  Points: 1004498

  More actions

  September 29, 2020 at 2:00 pm

  #3791634

  If you'd provide your example data in a "Readily Consumable Format" (please see the article at the first link in my signature line below for one way to do that), one or or more of us can show you how to do this in two shakes.

  --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)