Tsql Query help

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  June 19, 2017 at 7:50 am

  #331180

  Hi guys,
  I am working on a process where the query to be written seems little tricky to me. I have a table with following structure:

  Org_id  amount    date
  Dmat_billing   500.00    06 July 2017
  Dmat_inv   725.00   25 June 2017
  Dmat_billing   255.00   14 June 2017
  Dmat_expense  1050.00   18 May 2017
  -- and so on...

  Now i need my output as follows:
  (Taking first four rows as example)
  Billing    inv          expense
  755.00   725.09   1050.00

  Basically summation of amount based on org_id having everything after "_".

• Luis Cazares

  SSC Guru

  Points: 183706

  More actions

  June 19, 2017 at 8:07 am

  #1947250

  sqlenthu 89358 - Monday, June 19, 2017 7:50 AM

  Hi guys,
  I am working on a process where the query to be written seems little tricky to me. I have a table with following structure:

  Org_id  amount    date
  Dmat_billing   500.00    06 July 2017
  Dmat_inv   725.00   25 June 2017
  Dmat_billing   255.00   14 June 2017
  Dmat_expense  1050.00   18 May 2017
  -- and so on...

  Now i need my output as follows:
  (Taking first four rows as example)
  Billing    inv          expense
  755.00   725.09   1050.00

  Basically summation of amount based on org_id having everything after "_".

  Check the following article to learn how to do it.
  http://www.sqlservercentral.com/articles/T-SQL/63681/

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• Thom A

  SSC Guru

  Points: 99134

  More actions

  June 19, 2017 at 8:15 am

  #1947252

  Perhaps something like:
  CREATE TABLE #Sample
  (Org_ID varchar(20),
  Amount decimal(12,2),
  date date); --That is not confusing... </sarcasm> ;)
GO

  INSERT INTO #Sample
VALUES
  ('Dmat_billing',500.00,'20170706'),
  ('Dmat_inv',725.00,'20170625'),
  ('Dmat_billing',255.00,'20170614'),
  ('Dmat_expense',1050.00,'20170518'),
  ('Lfat_billing', 500.00,'20170619'), --Add some more sample data
  ('Lfat_inv',170.00,'20170628');
GO

  SELECT *
FROM #Sample;
GO

  WITH Orgs AS (
  SELECT LEFT(S.Org_ID,charindex('_',S.Org_ID) -1) AS Org,
        RIGHT(S.Org_ID, len(S.Org_ID) - charindex('_',S.Org_ID)) AS Category,
        Amount, date
  FROM #Sample S)
SELECT O.Org,
       SUM(CASE WHEN O.Category= 'billing' Then O.Amount ELSE 0.00 END) AS Billing,
       SUM(CASE WHEN O.Category= 'inv' Then O.Amount ELSE 0.00 END) AS Inv,
       SUM(CASE WHEN O.Category= 'expense' Then O.Amount ELSE 0.00 END) AS Expense
FROM Orgs O
GROUP BY O.Org;
GO 

  DROP TABLE #Sample;
GO


  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  June 19, 2017 at 11:55 pm

  #1947338

  Thanks to both of you guys. However there is no limited number of Org_ids. It can vary and change from time to time.
  In between, I replied many hours back but seems somehow it was not posted.

• Thom A

  SSC Guru

  Points: 99134

  More actions

  June 20, 2017 at 2:12 am

  #1947345

  sqlenthu 89358 - Monday, June 19, 2017 11:55 PM

  Thanks to both of you guys. However there is no limited number of Org_ids. It can vary and change from time to time.
  In between, I replied many hours back but seems somehow it was not posted.

  What do you mean by there isn't a limit to the number of Org_ids. I made the assumption that in your (very small) sample data, dmat was the id and billing/inv/expense was the field description. If so, then both mine and Luis' offered solutions will work. Notice in my small example I add an extra id, lfat, which shows in the end data as well, with values for billing, inv and espense. Is this not what you are looking for

  If not, perhaps you could provide more comprehensive sample data (with DLM and DDL, like I have done) and your expected output, so that someone can provide you with a more comprehensive answer.

  Thom~

  Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
  Larnu.uk

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  June 21, 2017 at 10:26 am

  #1947597

  Thom, both urs and Luis' query gave me a fair idea of how I should start. However the table gets populated on daily basis and the report is needed on monthly basis. On any month, the no of Org_id can be different. So needed to populate the dynamic columns and then work with dynamic query with pivot function. It was something like:

  Declare @cols = <query to get dynamic columns based on no of Org_ids for xml path>
  Declare @qry = <query using pivot function>

  Sp_executesql @qry

• Jeff Moden

  SSC Guru

  Points: 1004434

  More actions

  June 21, 2017 at 3:03 pm

  #1947648

  sqlenthu 89358 - Wednesday, June 21, 2017 10:26 AM

  Thom, both urs and Luis' query gave me a fair idea of how I should start. However the table gets populated on daily basis and the report is needed on monthly basis. On any month, the no of Org_id can be different. So needed to populate the dynamic columns and then work with dynamic query with pivot function. It was something like:

  Declare @cols = <query to get dynamic columns based on no of Org_ids for xml path>
  Declare @qry = <query using pivot function>

  Sp_executesql @qry

  So see Part 2 of the previously listed article.
  http://www.sqlservercentral.com/articles/Crosstab/65048/

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

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  June 21, 2017 at 9:34 pm

  #1947678

  Thanks Jeff. Very helpful and interesting.

• Jeff Moden

  SSC Guru

  Points: 1004434

  More actions

  June 22, 2017 at 7:45 am

  #1947759

  sqlenthu 89358 - Wednesday, June 21, 2017 9:34 PM

  Thanks Jeff. Very helpful and interesting.

  Thanks for the feedback.  Just to add to the info in that article a bit, remember that if you have unique character based values, you can use MAX() instead of SUM() to pivot them using the very same technique.

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

• sqlenthu 89358

  Hall of Fame

  Points: 3069

  More actions

  June 25, 2017 at 12:06 pm

  #1948082

  Jeff Moden - Thursday, June 22, 2017 7:45 AM

  sqlenthu 89358 - Wednesday, June 21, 2017 9:34 PM

  Thanks Jeff. Very helpful and interesting.

  Thanks for the feedback.  Just to add to the info in that article a bit, remember that if you have unique character based values, you can use MAX() instead of SUM() to pivot them using the very same technique.

  Cool. Thanks. Guess what looks like I am going to spend few days working on these samples to get more understanding of it's functioning. Seems like a good working week.