Solved

TRANSFORM, PIVOT - implement in SQL

Posted on 2004-10-01
2
938 Views
Last Modified: 2012-06-27
Here is my access query:

TRANSFORM Sum(tblBudgetAmount.LocalAmount) AS SumOfFunctionalAmount
SELECT tblBudgetItem.CostCenterID, tblBudgetItem.GL as GL1,tblBudgetItem.ProjectID,
      Sum(tblBudgetAmount.LocalAmount) AS ColumnTotal, tblAcctCode_0.sStatus
FROM tblAcctCode_0
INNER JOIN (tblBudgetItem
INNER JOIN tblBudgetAmount ON tblBudgetItem.ID = tblBudgetAmount.BudItemID)
                  ON tblAcctCode_0.sCodeID = tblBudgetItem.GL
Where tblBudgetItem.CostCenterID ='385' and tblBudgetItem.ProjectID='1537'
      and left(tblBudgetItem.GL,2) like '62' and tblBudgetItem.ActivityID='50770'
      and tblBudgetItem.FundID='10' and tblBudgetAmount.FiscalYear ='2005'
GROUP BY tblBudgetItem.GL, tblBudgetItem.CostCenterID, tblBudgetItem.ProjectID, tblBudgetItem.GL, tblAcctCode_0.sStatus PIVOT tblBudgetItem.FundingSource


How would I go about converting this to SQL?
0
Comment
Question by:TWBit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 6

Expert Comment

by:mcp111
ID: 12203837
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 total points
ID: 12203900
Wait for SQL Server 2005, when this type of query will be implemented? ;)

Seriously, SQL Server does not innately support these types of queries, and a fair amount has been written on how to emulate them.  Usually, it will involve some custom coding and/or dynamic SQL.

Working from what I see here, what I would probably do is:

1)  Since you PIVOT is the FundingSource, this will be key.  Dynamically create an SQL Statement to generate the resulting data, using the information from the base SELECT as the source of the CASE statements needed.  This could be something like:

Declare @sqlCase varchar(7000)

Set @sqlCase = ''

Select @sqlCase = @sqlCase + 'Sum(CASE WHEN bi.Fundingsource = ''' + Fundingsource + ''' THEN ba.LocalAmount ELSE 0 END) AS [' + Fundingsource +, '
FROM
    (Select Distinct bi.Fundingsource From tblBudgetItem bi
      INNER JOIN tblBudgerAmount ba ON bi.ID = ba.BudItemID
      INNER JOIN tblAccountCode_0 ac ON tblBudgetItem.GL = tblAccountCode_0.sCodeID
      WHERE bi.CostCenterID ='385'
            and bi.ProjectID='1537'
            and left(bi.GL,2) = '62'
            and bi.ActivityID='50770'
            and bi.FundID='10'
            and ba.FiscalYear ='2005'
    ) Tbl1

Declare @sql varchar(8000)

Set @sql = 'SELECT bi.CostCenterID, bi.GL, bi.ProjectID, ' + @sqlCase + ' ac.sStatus FROM  tblBudgetItem bi
      INNER JOIN tblBudgerAmount ba ON bi.ID = ba.BudItemID
      INNER JOIN tblAccountCode_0 ac ON tblBudgetItem.GL = tblAccountCode_0.sCodeID
      WHERE bi.CostCenterID =''385''
            and bi.ProjectID=''1537''
            and left(bi.GL,2) = ''62''
            and bi.ActivityID=''50770''
            and bi.FundID=''10''
            and ba.FiscalYear =''2005''
     GROUP BY tblBudgetItem.GL, tblBudgetItem.CostCenterID, tblBudgetItem.ProjectID, tblBudgetItem.GL, tblAcctCode_0.sStatus'

EXEC @sql
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question