Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

TRANSFORM, PIVOT - implement in SQL

Posted on 2004-10-01
2
Medium Priority
?
946 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:
Brendt Hess earned 375 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

Technology Partners: 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

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…
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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