Follow-up question, Dynamic PIVOT query

Previous question here.

In the previous question, I forgot to mention that my production table was not exactly as listed because I assumed I could make the modifications I needed to the final query to pass in an additional variable @Product (values of 'Gas', 'Oil', 'Water') and select the appropriate column using a Case statement.  So, the following query works.
SET DATEFORMAT mdy;

Declare @Product nvarchar(5)
Declare @StartDate datetime2
Declare @EndDate datetime2
SET @Product = 'Gas'
Set @StartDate = '2014-09-01'
SET @EndDate = '2014-09-30'

--Define the string of dates to use in the Pivot clause
DECLARE @docDatesTable TABLE
(
	[docDate] Datetime
);

INSERT INTO @docDatesTable
SELECT DISTINCT docDate FROM tbl_sysProduction
WHERE docDate >= @StartDate AND docDate < DATEADD(day, 1, @EndDate);

--Build the String containing the date values to be used in the pivot
DECLARE @docDates VARCHAR(MAX);
SELECT @docDates = COALESCE(@docDates + '],[', '') +
		  (LEFT(CONVERT(VARCHAR, [docDate], 101),6) + RIGHT(YEAR([docDate]),2))
FROM @docDatesTable ORDER BY [docDate]

--Build the dynamic SQL String
DECLARE @sql NVARCHAR(MAX);
SET @sql = 'SELECT * FROM (
SELECT IsNull(W.Bolo_ID, F.Bolo_ID) as Bolo_ID
, IsNull(W.Common_Name, F.Facility_Name) as Well_Facility
, P.[Entity_ID]
, P.[docDate]
, Volume = [Gas] 
FROM tbl_sysProduction as P
LEFT JOIN tbl_sysWells as W ON P.Entity_ID = W.Entity_ID
LEFT JOIN tbl_sysFacilities as F on P.Entity_ID = F.Entity_ID
) AS [SubTable]
PIVOT
(
     SUM([Volume])
FOR [docDate] IN ([' + @docDates + '])
) AS [Pivot]
ORDER BY Bolo_ID, Well_Facility;
';

EXEC sp_executesql @sql;

Open in new window

But when I replace this line:
, Volume = [Gas]

Open in new window

with:
, Volume = CASE WHEN ' + @Product + ' = ''Gas'' THEN [Gas]
                WHEN ' + @Product + '= ''Oil'' THEN [Oil]
                WHEN ' + @Product + '= ''Water'' THEN [Water]
                ELSE NULL END

Open in new window

I get an error message:
Msg 8114, Level 16, State 5, Line1
Error converting data type varchar to float

I also tried it with:
, Volume = CASE WHEN @Product = ''Gas'' THEN [Gas]
                WHEN @Product = ''Oil'' THEN [Oil]
                WHEN @Product = ''Water'' THEN [Water]
                ELSE NULL END

Open in new window

but that raised an error also:
Must declare variable "@Product"

So, the question is, how do I pass in a value for the Product (@Product) and retrieve the correct column from tbl_sysProduction based on that value in the dynamic SQL Statement?
LVL 50
Dale FyeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Sytech SolutionsCommented:
Hi Dale

Do you mind posting the design of the table so I can see what the data types for the Gas, Oil and Water columns are?

Thanks
0
Sytech SolutionsCommented:
Bit of a stab in the dark without the tables but try this:

SET @sql = 'SELECT * FROM (
SELECT IsNull(W.Bolo_ID, F.Bolo_ID) as Bolo_ID
, IsNull(W.Common_Name, F.Facility_Name) as Well_Facility
, P.[Entity_ID]
, P.[docDate]
, Volume = CASE ''' + @Product + ''' WHEN ''Gas'' THEN [Gas]
                WHEN  ''Oil'' THEN [Oil]
                WHEN ''Water'' THEN [Water]
                ELSE NULL END
FROM tbl_sysProduction as P
LEFT JOIN tbl_sysWells as W ON P.Entity_ID = W.Entity_ID
LEFT JOIN tbl_sysFacilities as F on P.Entity_ID = F.Entity_ID
) AS [SubTable]
PIVOT
(
     SUM([Volume])
FOR [docDate] IN ([' + @docDates + '])
) AS [Pivot]
ORDER BY Bolo_ID, Well_Facility;
';

Open in new window


Thanks
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeAuthor Commented:
And your name is?

CREATE TABLE [dbo].[tbl_sysProduction](
	[Prod_ID] [int] IDENTITY(1,1) NOT NULL,
	[Entity_ID] [int] NOT NULL,
	[docDate] [datetime] NOT NULL,
	[Oil] [float] NULL,
	[Gas] [float] NULL,
	[Water] [float] NULL,
	[TimeStamp] [timestamp] NOT NULL,
 CONSTRAINT [PK_tbl_sysProduction] PRIMARY KEY CLUSTERED 
(
	[Prod_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Open in new window

Although I've accessed data from SQL Server a lot in the past, I've only recently really started building my own, so selection of float may have been the wrong datatype to emulate a double in Access.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Sytech SolutionsCommented:
it's Ben. Float is correct for a double.

Thanks
0
Dale FyeAuthor Commented:
That took care of it.  I should have done a SELECT on the value of @SQL before trying to execute it and it would have been obvious.
0
Sytech SolutionsCommented:
Yay excellent.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.