PIVOT Query Problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

PIVOT Query Problem

Author

Topic

manishchourasia2000
Starting Member

1 Post

Posted - 2010-08-06 : 03:39:41

Dear Friends,

I am trying to make the PIVOT Query but it is giving me error

Msg 107, Level 15, State 1, Line 17
The column prefix 'T4' does not match with a table name or alias name used in the query.
Msg 107, Level 15, State 1, Line 18
The column prefix 'T4' does not match with a table name or alias name used in the query.

Query :

select * from
(select
T0.[CardCode], T0.[CardName], T0.[NumAtCard] as BillNo, T0.[DocNum],
T0.[DocDate] as BillDate,T0.[Series], T3.[SeriesName] ,
T3.SeriesName + '/' + Cast(T0.[DocNum] as varchar) as VoucherNo,
T2.[TaxId3] as ServiceTaxNo,'' as Category,
T0.[DocTotal] as TotalBillValue,T4.TaxSum,T4.StaCode
from
OPCH T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
INNER JOIN CRD7 T2 ON T1.CardCode = T2.CardCode
INNER JOIN NNM1 T3 ON T0.Series = T3.Series
INNER JOIN PCH4 T4 ON T0.DocEntry = T4.DocEntry
where T2.[AddrType] ='S' and T0.[DocType] ='S' ) ps
PIVOT
(
SUM (T4.TaxSum)
FOR T4.StaCode IN
( [N02BED10],[N02T0025],[N02T0039],[N02T0043],[N02T0065] )
) AS pvt

Thanks In Advance,

Manish

Thanks & Regards
Manish

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-08-06 : 04:05:20

[code]
SELECT *
FROM
(
SELECT
T0.[CardCode],
T0.[CardName],
T0.[NumAtCard] AS BillNo,
T0.[DocNum],
T0.[DocDate] AS BillDate,
T0.[Series],
T3.[SeriesName] ,
T3.SeriesName + '/' + CAST(T0.[DocNum] AS varchar) AS VoucherNo,
T2.[TaxId3] AS ServiceTaxNo,
'' AS Category,
T0.[DocTotal] AS TotalBillValue,
T4.TaxSum,
T4.StaCode
FROM
OPCH T0
INNER JOIN OCRD T1 ON T0.CardCode = T1.CardCode
INNER JOIN CRD7 T2 ON T1.CardCode = T2.CardCode
INNER JOIN NNM1 T3 ON T0.Series = T3.Series
INNER JOIN PCH4 T4 ON T0.DocEntry = T4.DocEntry
WHERE T2.[AddrType] = 'S'
AND T0.[DocType] = 'S'
) ps
PIVOT
(
SUM (~~T4.~~TaxSum)
FOR ~~T4.~~StaCode IN
( [N02BED10],[N02T0025],[N02T0039],[N02T0043],[N02T0065] )
) AS pvt
[/code]

KH
[spoiler]Time is always against us[/spoiler]

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-08-09 : 03:12:29

For dynamic number of values, use
http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources