Problem with PIVOT operator

Question

Post reply

Problem with PIVOT operator

luissantos

SSCertifiable

Points: 7391
More actions
February 16, 2011 at 9:01 am

#234552

Hello comunity
i have the following script :
DECLARE @PivotColumnHeaders VARCHAR(MAX)
SELECT @PivotColumnHeaders =
COALESCE(
@PivotColumnHeaders + ',[' + cast(nome as varchar) + ']',
'[' + cast(nome as varchar)+ ']'
)
FROM ft
DECLARE @PivotTableSQL VARCHAR(MAX)
SET @PivotTableSQL = N'
SELECT *
FROM (
Select cast(month(fdata) as varchar) as [mes],
(RTRIM(FT.NOME) + CAST(ft.no AS VARCHAR)) as [nome],
(ft.eivain1+ft.eivain2+ft.eivain3+ft.eivain4+ft.eivain5+ft.eivain6+ft.eivain7+ft.eivain8+ft.eivain9) as [Vendas]
from ft (nolock)
where ft.anulado = 0 and year(ft.fdata)=2011 And (ft.tipodoc<>4) and ft.estab = 0
--group by (RTRIM(FT.NOME) + CAST(ft.no AS VARCHAR))--,month(ft.fdata)
) AS PivotData
PIVOT (
SUM(vendas)
FOR [mes] IN (
' + @PivotColumnHeaders + '
)
) AS PivotTable
'
EXECUTE(@PivotTableSQL)
But , SQL server return the following error :
Msg 8156, Level 16, State 1, Line 17
The column 'Albertino Maciel ' was specified multiple times for 'PivotTable'.
I don´t understand why ?
I need to return for each customer and for each month for year 2011 the totals of sails.
Many thanks
Luis Santos

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

LutzM SSC Guru Points: 107049 More actions · Answer 1

Without any more details (like table def, sample data, expected result and the like) it's hard to tell.

My guess would be there are more than one rows with nome ='Albertino Maciel' in your ft table.

You might need to use a GROUP BY nome when populating @PivotColumnHeaders.

You might be able to locate the issue when using PRINT(@PivotTableSQL) instead of EXECUTE(@PivotTableSQL).

Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question[/url]
How to post performance related questions[/url]
Links for Tally Table [/url] , Cross Tabs [/url] and Dynamic Cross Tabs [/url], Delimited Split Function[/url]

Eralper SSCarpal Tunnel Points: 4438 More actions · Answer 2

Hello,

I guess the problem is where you select the column names for pivot

I believe it will solve the problem if you use DISTINCT

Please refer to SQL dynamic pivot table example where the table columns are concateneted with distinct key

I hope that helps

Eralper
SQL Server and T-SQL Tutorials and Articles
Microsoft Certification and Certification Exams