[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Help with pivot

Harry Strybos

3/18/2007 9:57:00 PM

Hi All

I have constructed the following query from Sql 2005 help sample. Pivot
tables are new to me.

Essentially, I have data the looks like this:

BDMName DateCollected ActiveMembers

------------------------------------------------------------- -----------------------
-------------

David Connell 2006-10-31 00:00:00 33411

David Connell 2006-11-30 00:00:00 32169

David Connell 2006-12-31 00:00:00 31778

David Connell 2007-01-31 00:00:00 31565

David Connell 2007-02-28 00:00:00 31775

Scott Jackson 2006-10-31 00:00:00 13396

Scott Jackson 2006-11-30 00:00:00 13232

Scott Jackson 2006-12-31 00:00:00 12287

Scott Jackson 2007-01-31 00:00:00 12234

Scott Jackson 2007-02-28 00:00:00 12099

Paul Richardson 2006-10-31 00:00:00 27613

Paul Richardson 2006-11-30 00:00:00 27354

Paul Richardson 2006-12-31 00:00:00 26217

Paul Richardson 2007-01-31 00:00:00 26488

Paul Richardson 2007-02-28 00:00:00 26628

--example only, aliases will be created in code

SELECT Salesperson, [1] As 'Jun', [2] AS 'Jul', [3] AS 'Aug', [4] AS 'Sep',
[5] AS 'Oct'

FROM

(SELECT RTRIM(B.FirstName) + ' ' + B.LastName AS Salesperson,

S.DateCollected,

S.ActiveMembers AS Customers

FROM dbo.BDMStat AS S

INNER JOIN dbo.BDM AS B ON S.BDM_ID = B.ID

WHERE S.DateCollected IN

(SELECT DISTINCT Top(5) DateCollected FROM dbo.BDMStat

ORDER BY DateCollected DESC) AS SourceTable

PIVOT

(

ActiveMembers

FOR DateCollected IN ([1], [2], [3], [4], [5]) ----- error here "Incorrect
syntax near the keyword 'FOR'"

) AS PivotTable

I want the data to look like this:

Salesperson, Date1, Date2, Date3, Date4, Date5

David Connel, 33411, 32169, 31778 etc.............

Thanks for any help
2 Answers

Plamen Ratchev

3/19/2007 3:37:00 AM

0

When you use the PIVOT operator you have to use an aggregate function for
the column that is being aggregated. Also, you already aliased the column
ActiveMembers as Customers, so you should use Customers.

See if this will help you to get the results:

-- This will break down by year
SELECT Salesperson,
SaleYear,
[1] AS 'Jan',
[2] AS 'Feb',
[3] AS 'Mar',
[4] AS 'Apr',
[5] AS 'May',
[6] AS 'Jun',
[7] AS 'Jul',
[8] AS 'Aug',
[9] AS 'Sep',
[10] AS 'Oct',
[11] AS 'Nov',
[12] AS 'Dec'
FROM
(SELECT RTRIM(B.FirstName) + ' ' + B.LastName AS Salesperson,
YEAR(S.DateCollected) AS SaleYear,
MONTH(S.DateCollected) AS SaleMonth,
S.ActiveMembers AS Customers
FROM dbo.BDMStat AS S
INNER JOIN dbo.BDM AS B ON S.BDM_ID = B.ID
WHERE S.DateCollected IN
(SELECT DISTINCT Top(5) DateCollected
FROM dbo.BDMStat
ORDER BY DateCollected DESC)) AS SourceTable
PIVOT
(SUM(Customers) FOR SaleMonth IN ([1], [2], [3], [4], [5], [6], [7], [8],
[9], [10], [11], [12]) ) AS PT;

-- No year break down
SELECT Salesperson,
[1] AS 'Jan',
[2] AS 'Feb',
[3] AS 'Mar',
[4] AS 'Apr',
[5] AS 'May',
[6] AS 'Jun',
[7] AS 'Jul',
[8] AS 'Aug',
[9] AS 'Sep',
[10] AS 'Oct',
[11] AS 'Nov',
[12] AS 'Dec'
FROM
(SELECT RTRIM(B.FirstName) + ' ' + B.LastName AS Salesperson,
MONTH(S.DateCollected) AS SaleMonth,
S.ActiveMembers AS Customers
FROM dbo.BDMStat AS S
INNER JOIN dbo.BDM AS B ON S.BDM_ID = B.ID
WHERE S.DateCollected IN
(SELECT DISTINCT Top(5) DateCollected
FROM dbo.BDMStat
ORDER BY DateCollected DESC)) AS SourceTable
PIVOT
(SUM(Customers) FOR SaleMonth IN ([1], [2], [3], [4], [5], [6], [7], [8],
[9], [10], [11], [12]) ) AS PT;

HTH,

Plamen Ratchev
http://www.SQL...

Harry Strybos

3/19/2007 9:40:00 PM

0

Thank You :)

"Plamen Ratchev" <Plamen@SQLStudio.com> wrote in message
news:uorCGgdaHHA.4396@TK2MSFTNGP06.phx.gbl...
> When you use the PIVOT operator you have to use an aggregate function for
> the column that is being aggregated. Also, you already aliased the column
> ActiveMembers as Customers, so you should use Customers.
>
> See if this will help you to get the results:
>
> -- This will break down by year
> SELECT Salesperson,
> SaleYear,
> [1] AS 'Jan',
> [2] AS 'Feb',
> [3] AS 'Mar',
> [4] AS 'Apr',
> [5] AS 'May',
> [6] AS 'Jun',
> [7] AS 'Jul',
> [8] AS 'Aug',
> [9] AS 'Sep',
> [10] AS 'Oct',
> [11] AS 'Nov',
> [12] AS 'Dec'
> FROM
> (SELECT RTRIM(B.FirstName) + ' ' + B.LastName AS Salesperson,
> YEAR(S.DateCollected) AS SaleYear,
> MONTH(S.DateCollected) AS SaleMonth,
> S.ActiveMembers AS Customers
> FROM dbo.BDMStat AS S
> INNER JOIN dbo.BDM AS B ON S.BDM_ID = B.ID
> WHERE S.DateCollected IN
> (SELECT DISTINCT Top(5) DateCollected
> FROM dbo.BDMStat
> ORDER BY DateCollected DESC)) AS SourceTable
> PIVOT
> (SUM(Customers) FOR SaleMonth IN ([1], [2], [3], [4], [5], [6], [7], [8],
> [9], [10], [11], [12]) ) AS PT;
>
> -- No year break down
> SELECT Salesperson,
> [1] AS 'Jan',
> [2] AS 'Feb',
> [3] AS 'Mar',
> [4] AS 'Apr',
> [5] AS 'May',
> [6] AS 'Jun',
> [7] AS 'Jul',
> [8] AS 'Aug',
> [9] AS 'Sep',
> [10] AS 'Oct',
> [11] AS 'Nov',
> [12] AS 'Dec'
> FROM
> (SELECT RTRIM(B.FirstName) + ' ' + B.LastName AS Salesperson,
> MONTH(S.DateCollected) AS SaleMonth,
> S.ActiveMembers AS Customers
> FROM dbo.BDMStat AS S
> INNER JOIN dbo.BDM AS B ON S.BDM_ID = B.ID
> WHERE S.DateCollected IN
> (SELECT DISTINCT Top(5) DateCollected
> FROM dbo.BDMStat
> ORDER BY DateCollected DESC)) AS SourceTable
> PIVOT
> (SUM(Customers) FOR SaleMonth IN ([1], [2], [3], [4], [5], [6], [7], [8],
> [9], [10], [11], [12]) ) AS PT;
>
> HTH,
>
> Plamen Ratchev
> http://www.SQL...
>
>
>