CODEWITHSUNDEEP
sql-server-2008
Antony
Antony

Reputation: 83

Another Pivot with Dynamic Fields

I have a view defined as follows:

Select IdSezioneDonatore, NumeroDonatore, Anno, DonaAnno
From dbo.DonazioniAnnue
Where (Anno>= YEAR(GETDATE())-4)

I will return the following fields and values:

IdSezioneDonatore      NumeroDonatore   Anno   DonaAnno
    2850                   3624          2009     3
    2850                   5585          2009     1
    2850                   3624          2010     2
    2850                   5586          2010     1
    2850                   3624          2011     1
    2850                   5586          2011     1
    ....                   .....         ....    ...

How can I do to get a result like this?:

 IdSezioneDonatore      NumeroDonatore   Anno 2009  Anno2010 Anno 2011 Anno 2012 Anno 2013
    2850                   3624               3         2         0        0         0
    2850                   5585               1         0         0        0         1
    2850                   5586               0         1         1        0         1
    ....                   .....             ...       ...       ...      ...       ...

Tank far all

Upvotes: 0

Views: 50

Answers (1)

Ram Das
Ram Das

Reputation: 358

DECLARE @QUERY NVARCHAR(MAX)

DECLARE @Annos TABLE(Anno INT)

INSERT INTO @Annos
SELECT DISTINCT Anno FROM TEST

DECLARE @Annuals VARCHAR(MAX)
DECLARE @Annuals_New VARCHAR(MAX)

SELECT @Annuals = COALESCE(+@Annuals+'],[' ,'[') +CONVERT(VARCHAR(10),A.Anno)
FROM @Annos A

SET @Annuals_New = @Annuals+']' 
SELECT @QUERY='SELECT * FROM TEST PIVOT (MAX(DonaAnno) FOR Anno IN ('+@Annuals_New+'))AS [pivot]'

EXEC SP_EXECUTESQL  @QUERY

This would help. ;-)

Upvotes: 1

Related Questions