SQL with Manoj

Hi! Pretty cool!!!!

Could you explain how could I save the results of the pivot into a temporary table?

Thanks very much!! Maurik

Thanks a bunch! You rock! 🙂 I hadn’t figured out the different scope part at all. It works like a charm now.

Hi I have the following code that I am needing to replace the null values with 0
declare @col varchar(1000)
declare @sql varchar(2000)

select @col = COALESCE(@col + ‘, ‘,”) + QUOTENAME(FiscalYear)
from (select distinct FiscalYear from ##T2)t

set @sql = ‘
select ClientNumber, ClientName, WorkType,’ + @col + ‘
into ##T3
from (select ClientNumber, ClientName,WorkType,BillFees,FiscalYear from ##T2)p
PIVOT(Sum(BillFees) FOR FiscalYear IN ( ‘ + @col + ‘ )
)AS pvt
ORDER BY WorkType,ClientNumber’

exec (@sql)

(EXCUSE ALIGNMENT)
RESULT SET
ClientName WorkType 2010 2011
A D-1 1 NULL
B D-2 NULL 2
C D-3 NULL 3
A D-4 2 3
B D-5 2 3
C D-6 NULL 2
A D-7 23 3
B D-8 NULL 3
C D-9 3 3
A D-10 NULL 3

Please could you assist with where I should place the isnull(,0) or how I sould approach this?

Hi there, just in case anyone comes across this, when trying to use the above cod in a stored procedure you wll get an “invalid Object Name ##[Name of your Gobal temp Table]

this is because ssrs is tryig to parse the entire content of the stored procedure and cannot find where the ##table is created in the code. I got around this by creating the ##Table prior to exec@sql as shown below. This code will however only work if the number of dynamic columns can be determined before the code is executed. You could also add some code to get a count of the dynamically created columns and then add them to the table that way.

declare @col varchar(1000)
declare @col2 varchar(1000)
declare @sql varchar(2000)

select @col = COALESCE(@col + ‘, ‘,”) + ‘ISNULL(‘ + QUOTENAME(FiscalYear)+ ‘,0) AS ‘+QUOTENAME(FiscalYear)
from (select distinct FiscalYear from ##T2)t

select @col2 = COALESCE(@col2 + ‘, ‘,”) + QUOTENAME(FiscalYear)
from (select distinct FiscalYear from ##T2)t

create table ##T3
(ClientNumber varchar(60)
,ClientName Varchar (250)
,WorkType varchar(10)
,A decimal(16,2)
,B decimal (16,2)
)

set @sql = ‘insert into ##T3
select ClientNumber, ClientName, WorkType,’ + @col + ‘
from (select ClientNumber, ClientName,WorkType,BillFees,FiscalYear from ##T2)p
PIVOT(Sum(BillFees) FOR FiscalYear IN ( ‘ + @col2 + ‘ )
)AS pvt
ORDER BY WorkType,ClientNumber’

exec (@sql)

create table #T4
(ClientNumber varchar(60)
,ClientName Varchar (250)
,WorkType varchar(10)
,PriorYear decimal(16,2)
,CurrentYear decimal (16,2)
,Dif Decimal (16,2)
,per decimal(16,2)
)

insert into #T4 (ClientNumber,ClientName,WorkType,PriorYear,CurrentYear)
select * from ##T3

Please see this.
My Records are in the following form,

ClientNo FeeAmount FeeDec
—————————————————– ————————————————————–E9429 6300.00 Education
E9429 4500.00 Transportation
E9421 7100.00 Education
E9421 4500.00 Transportation
G9443 6750.00 Education
G9442 6900.00 Education
G9443 4500.00 Transportation
G9442 4500.00 Transportation
E9447 6300.00 Education
E9447 4500.00 Transportation

I Wan the Formated as Under.

ClientNo Education Transportation
———————————————————————————————————–
E9429 6300 4500
E9421 7100 4500
G9443 6750 4500

What I Should Change in the Below Code for the above result???

As I Am using Common Table Expression for two Different tables by reviewing ur solution.

I Tried this but its not working…

Thanks in Advance

;with FIRSTCTE
AS
(
SELECT
DISTINCT
A.ReceiptNo AS ReceiptNo,
Cl.No AS ClientNo,
A.FeeAmount AS FeeAmount
FROM
(
SELECT
T.Id AS ReceiptNo,
SUM(TL.Amount) AS FeeAmount
FROM
dbo.[Transaction] T
INNER JOIN dbo.TransactionLineItem TL
ON
(
TL.TransactionId = T.Id
)
GROUP BY
T.Id
)A
INNER JOIN dbo.[Transaction] T
ON
(
T.Id = A.ReceiptNo
)
INNER JOIN dbo.TransactionLineItem TLI
ON
(
TLI.TransactionId = T.Id
)
INNER JOIN dbo.BusinessEntity BE
ON
(
BE.Id= t.BusinessEntityId
)
INNER JOIN dbo.Client CL
ON
(
Cl.BusinessEntityId=BE.Id
)
WHERE
TLI.PeriodId=11

),

SECONDCTE
AS
(
SELECT
DISTINCT
T.Id AS ReceiptNo,
RE.Description AS FeeDec
FROM
dbo.[Transaction] T
INNER JOIN dbo.TransactionLineItem TLI
ON
(
TLI.TransactionId = T.Id
)
INNER JOIN dbo.Fee F
ON
(
F.Id = TLI.FeeId
)
INNER JOIN ReferenceData RE
ON
(
RE.Id = F.FeeTypeId
)
WHERE
TLI.PeriodId=11

)

SELECT
ClientNo,
[Edu],
[Tra]
FROM
(
SELECT
F.ClientNo AS ClientNo,
F.FeeAmount AS FeeAmount,
S.FeeDec AS FeeDec
FROM
FIRSTCTE F
JOIN SECONDCTE S
ON
F.ReceiptNo = S.ReceiptNo
)P
PIVOT
(
MAX(FeeAmount) FOR FeeDec IN([Edu],[Tra])
)AS PVt