Solved

TRANSFORM, PIVOT - implement in SQL

Posted on 2004-10-01
2
924 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
2 Comments
 
LVL 6

Expert Comment

by:mcp111
Comment Utility
0
 
LVL 32

Accepted Solution

by:
bhess1 earned 125 total points
Comment Utility
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

772 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now