Solved

TRANSFORM, PIVOT - implement in SQL

Posted on 2004-10-01
2
940 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how the fundamental information of how to create a table.

689 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