Pivot Table Help - 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

SQL Server 2008 Forums

Transact-SQL (2008)

Pivot Table Help

Author

Topic

ross_gt
Starting Member

23 Posts

Posted - 2011-05-31 : 12:09:52

I need help creating a pivot table for total 2011 YTD revenue by SHIPTO, SGNs and NSGNs numbers and names for Canada. Below is what I have so far, but I get the error message "Msg 102, Level 15, State 1, Line 7
Incorrect syntax near ')'."

USE Customer_Analytics

SELECT '2011 YTD Revenue' AS Canada, [SHIPTO], [SGN], [NSGN]
FROM
(SELECT [A.Extended Sales PMAR Amount], [A.Ship To Customer ID], [A.Order Date (YYYYMMDD)] [B.SGN_Number], [B.NSGN_Name]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted] AS A
JOIN [ReferenceData].[dbo].[AB] AS B) AS SourceTable
PIVOT
(
SUM([A.Extended Sales PMAR Amount])
FOR [A.Extended Sales PMAR Amount] IN ([SHIPTO], [SGN], [NSGN])) AS PivotTable;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-31 : 12:12:06

I think it's in your bracketing
select a.[Extended Sales PMAR Amount] not [a.Extended Sales PMAR Amount]
for all those columns

Jim

Everyday I learn something that somebody else already knew

ross_gt
Starting Member

23 Posts

Posted - 2011-05-31 : 12:16:27

Oh good catch. I fixed those changes; however I still get the message: Incorrect syntax near ')'.

I don't understand why.

USE Customer_Analytics

SELECT '2011 YTD Revenue' AS Canada, [SHIPTO], [SGN], [NSGN]
FROM
(SELECT A.[Extended Sales PMAR Amount], A.[Ship To Customer ID], A.[Order Date (YYYYMMDD)], B.[SGN_Number], B.[NSGN_Name]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted] AS A
JOIN [ReferenceData].[dbo].[AB] AS B) AS SourceTable
PIVOT
(
SUM(A.[Extended Sales PMAR Amount])
FOR A.[Extended Sales PMAR Amount] IN ([SHIPTO], [SGN], [NSGN])) AS PivotTable;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-31 : 12:28:46

Okay, it looks a little clearer now, but this is a guess

SELECT '2011 YTD Revenue' AS Canada, [SHIPTO], [SGN], [NSGN]
FROM
(SELECT A.[Extended Sales PMAR Amount], A.[Ship To Customer ID], A.[Order Date (YYYYMMDD)], B.[SGN_Number], B.[NSGN_Name]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted] AS A
INNER JOIN [ReferenceData].[dbo].[AB] AS B
ON <a.something = b.something>
) AS SourceTable

PIVOT
(
SUM(A.[Extended Sales PMAR Amount])
FOR A.[Extended Sales PMAR Amount] --<<< this part here should be the column that has 'Shipto','SGN','NSGN' as Values
IN ([SHIPTO], [SGN], [NSGN])
) AS PivotTable;

Jim

Everyday I learn something that somebody else already knew

ross_gt
Starting Member

23 Posts

Posted - 2011-05-31 : 12:39:19

Perfect! Thank you so much!

ross_gt
Starting Member

23 Posts

Posted - 2011-05-31 : 17:25:21

Actually, I have now run into a different problem. I now receive this message: Msg 102, Level 15, State 1, Line 12
Incorrect syntax near '('.

USE Customer_Analytics

SELECT '2011 YTD Revenue' AS Canada, [SHIPTO], [SGN], [NSGN]
FROM
(SELECT A.[Extended Sales PMAR Amount], A.[Country of Ultimate Destination Code], B.[SGN_Number], B.[NSGN_Name], B.[COUNTRY]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted] AS A
INNER JOIN [ReferenceData].[dbo].[AB] AS B
ON B.[COUNTRY] LIKE N'%Canada' AND A.[Country of Ultimate Destination Code] LIKE N'%Canada%'
AND A.[Order Date (YYYYMMDD)] >= '20110101' AND B.[SHIP_TO_CREATED_DATE] >= '20110101') AS SourceTable
PIVOT

(SUM(CAST([Extended Sales PMAR Amount] AS FLOAT))
FOR [Country of Ultimate Destination Code] IN ([SHIPTO],[SGN], [NSGN])) AS PivotTable;
Go to Top of Page
To

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-31 : 21:11:10

Change to


USE Customer_Analytics

SELECT '2011 YTD Revenue' AS Canada, [SHIPTO], [SGN], [NSGN]
FROM
(SELECT cast(A.[Extended Sales PMAR Amount] as float) as [Extended Sales PMAR Amount], A.[Country of Ultimate Destination Code], B.[SGN_Number], B.[NSGN_Name], B.[COUNTRY]
FROM [Customer_Analytics].[CleanData].[0607080910_sorted] AS A
INNER JOIN [ReferenceData].[dbo].[AB] AS B
ON B.[COUNTRY] LIKE N'%Canada' AND A.[Country of Ultimate Destination Code] LIKE N'%Canada%'
AND A.[Order Date (YYYYMMDD)] >= '20110101' AND B.[SHIP_TO_CREATED_DATE] >= '20110101') AS SourceTable
PIVOT

(SUM( CAST( [Extended Sales PMAR Amount] AS FLOAT) )
FOR [Country of Ultimate Destination Code] IN ([SHIPTO],[SGN], [NSGN])) AS PivotTable;

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-06-01 : 07:46:23


USE Customer_Analytics

SELECT '2011 YTD Revenue' AS Canada, [SHIPTO], [SGN], [NSGN]
FROM
(SELECT  A.[Country of Ultimate Destination Code], B.[SGN_Number], B.[NSGN_Name], B.[COUNTRY]
     ,CAST([Extended Sales PMAR Amount] AS FLOAT) as  [Extended Sales PMAR Amount]
 FROM       [Customer_Analytics].[CleanData].[0607080910_sorted] AS A
 INNER JOIN [ReferenceData].[dbo].[AB]                           AS B   
                ON B.[COUNTRY] LIKE N'%Canada' 
               AND A.[Country of Ultimate Destination Code] LIKE N'%Canada%'
               AND A.[Order Date (YYYYMMDD)] >= '20110101' 
               AND B.[SHIP_TO_CREATED_DATE] >= '20110101'
 ) AS SourceTable
PIVOT
(        
         SUM( [Extended Sales PMAR Amount])
            )
       FOR [Country of Ultimate Destination Code] IN ([SHIPTO],[SGN], [NSGN])
) AS PivotTable;

Jim

Everyday I learn something that somebody else already knew

ross_gt
Starting Member

23 Posts

Posted - 2011-06-01 : 11:16:23

Awesome! Thanks guys.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources