Help on this code - 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

General SQL Server Forums

New to SQL Server Programming

Help on this code

Author

Topic

ms115
Starting Member

5 Posts

Posted - 2015-03-24 : 09:36:38

I have created this with nothwind databse but i want pivot it quotename function to get all productName which works fine using print but on ruining the query itself without print it gives error can someone help here is code below

Declare @Columnames nvarchar(max)= ''
Declare @Sam nvarchar(max)= ''

select @Columnames += quotename(ProductName)
from dbo.Products
select Categories.CategoryName from (

SELECT Categories.CategoryName, dbo.Products.ProductName,
SUM(CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity)
* (1 - dbo.[Order Details].Discount) / 100) * 100) AS ProductSales
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID ON dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

GROUP BY Categories.CategoryName,dbo.Products.ProductName
) as Pivotdata pivot (
SUM(ProductSales) for ProductName in (' + @Columnames + ' )) as Pivoting

the @columnname still not working but give result of what to achieve using print

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 10:10:19

You need to use dynamic sql for that. Build up the whole query in a variable then use sp_executesql to run it

ms115
Starting Member

5 Posts

Posted - 2015-03-24 : 10:14:48

i have use sp_exec to execute but it giving error with columnnames

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 10:35:17

post the query using sp_executesql and the error message you get.

ms115
Starting Member

5 Posts

Posted - 2015-03-24 : 11:22:30

Declare @Columnames nvarchar(max)= ''
declare @Sam nvarchar(max)= ''

select @Columnames += quotename(dbo.Products.ProductName)
from dbo.Products
set @Columnames = left(@columnames, len(@columnames)-1)

set @Sam ='
select Categories.CategoryName from (

SELECT Categories.CategoryName, dbo.Products.ProductName,
SUM(CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity)
* (1 - dbo.[Order Details].Discount) / 100) * 100) AS ProductSales
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID ON dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

GROUP BY Categories.CategoryName,dbo.Products.ProductName
) as Pivotdata pivot (
SUM(ProductSales) for ProductName in (' + @Columnames + ' ) as Pivoting'

execute sp_executesql @sam

error:

Msg 102, Level 15, State 1, Line 15
Incorrect syntax near 'Aniseed Syrup'.
Msg 105, Level 15, State 1, Line 15
Unclosed quotation mark after the character string 'Zaanse koeken ) as Pivoting'.

or if i doing this:

Declare @Columnames nvarchar(max)= ''
declare @Sam nvarchar(max)= ''

select @Columnames += quotename(dbo.Products.ProductName)
from dbo.Products

--set @Columnames = left(@columnames, len(@columnames)-1)
--set @Sam ='

select Categories.CategoryName from (

SELECT Categories.CategoryName, dbo.Products.ProductName,
SUM(CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity)
* (1 - dbo.[Order Details].Discount) / 100) * 100) AS ProductSales
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID ON dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID

GROUP BY Categories.CategoryName,dbo.Products.ProductName
) as Pivotdata pivot (
SUM(ProductSales) for ProductName in (' + @Columnames + ' )) as Pivoting

error:

Msg 102, Level 15, State 1, Line 24
Incorrect syntax near ' + @Columnames + '.

ms115
Starting Member

5 Posts

Posted - 2015-03-24 : 11:52:41

This works fine but i don't want to be listing all productname in the table

select CategoryName,
[Chai] as ChaiProduct,
[Chang] as ChangProduct,
[Pavlova] as PavProduct,
[Spegesild] as SeaProduct
from
(
SELECT Categories.CategoryName, dbo.Products.ProductName,
SUM(CONVERT(money, (dbo.[Order Details].UnitPrice * dbo.[Order Details].Quantity)
* (1 - dbo.[Order Details].Discount) / 100) * 100) AS ProductSales
FROM dbo.Categories INNER JOIN
dbo.Products ON dbo.Categories.CategoryID = dbo.Products.CategoryID INNER JOIN
dbo.Orders INNER JOIN
dbo.[Order Details] ON dbo.Orders.OrderID = dbo.[Order Details].OrderID ON dbo.Products.ProductID = dbo.[Order Details].ProductID INNER JOIN
dbo.Customers ON dbo.Orders.CustomerID = dbo.Customers.CustomerID
WHERE (dbo.Orders.ShippedDate BETWEEN '19970101' AND '19971231')
GROUP BY Categories.CategoryName,dbo.Products.ProductName
) as PivotData pivot
(
SUM(ProductSales) for ProductName in (chai,chang,Pavlova,Spegesild)) as Pivoting

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-24 : 11:56:05

Ok there are a few problems here:

first of all, this syntax:


select @Columnames += quotename(dbo.Products.ProductName)

is not supported by SQL Server, though it usually works. It *can* fail though. You might want to replace this with


set @columnnames = (
    select ...
    from  ...
    for xml path('')

Second, you have not put a comma between the column names. So you need something like:


set @columnnames = stuff(
(
    select ',' + quotename(..)
    from  ...
    for xml path('')
), 1, 1, '')

When you have those changes working, add a print @sam statement before the exececute sp_executesql so we can see what code you have generated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources