January 23, 2014 at 10:05 pm
Following script works fine. But I want one more aggregate function in new rows.
CREATE TABLE dbo.Products
(
ProductID INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL UNIQUE
/* other columns */
);
INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');
CREATE TABLE dbo.OrderDetails
(
OrderID INT,
ProductID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT
);
INSERT dbo.OrderDetails VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1),
(3, 3, 1);
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(Name)
FROM (SELECT p.Name FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
GROUP BY p.Name) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT p.Name, o.Quantity
FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
) AS j
PIVOT
(
SUM(Quantity) FOR Name IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
January 23, 2014 at 10:19 pm
something like following;
SELECT p.[foo], p.[bar], p.[kin]
FROM
(
SELECT p.Name, o.Quantity
FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
) AS j
PIVOT
(
count(Quantity) FOR Name IN ([foo],[bar],[kin])
) AS p
PIVOT
(
avg(Quantity) FOR Name IN ([foo],[bar],[kin])
) AS p;
January 23, 2014 at 11:03 pm
IF OBJECT_ID('OrderDetails') IS NOT NULL
DROP TABLE OrderDetails;
IF OBJECT_ID('Products') IS NOT NULL
DROP TABLE Products;
CREATE TABLE dbo.Products
(
ProductID INT PRIMARY KEY,
Name NVARCHAR(255) NOT NULL UNIQUE
/* other columns */
);
INSERT dbo.Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');
CREATE TABLE dbo.OrderDetails
(
OrderID INT,
ProductID INT NOT NULL
FOREIGN KEY REFERENCES dbo.Products(ProductID),
Quantity INT
);
INSERT dbo.OrderDetails VALUES
(1, 1, 1),
(1, 2, 2),
(2, 1, 1),
(3, 3, 1);
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX),@columns1 NVARCHAR(MAX),@columns2 NVARCHAR(MAX);
SELECT @columns = N'',@columns1 =N'',@columns2 = N'';
SELECT @columns += N', ' + QUOTENAME(Name),
@columns1 += N', ' + QUOTENAME(Name+'1'),
@columns2 += N', '+'SUM(ISNULL('+QUOTENAME(Name)+',0))'+QUOTENAME(Name)+',' +'SUM(ISNULL('+QUOTENAME(Name+'1')+',0))'+QUOTENAME(Name+'1')
FROM (SELECT p.Name FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
GROUP BY p.Name) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns2, 1, 2, '') + '
FROM
(
SELECT p.Name, o.Quantity, P.Name+''1'' AS Name1 , o.Quantity AS Quantity1
FROM dbo.Products AS p
INNER JOIN dbo.OrderDetails AS o
ON p.ProductID = o.ProductID
) AS j
PIVOT
(
SUM(Quantity) FOR Name IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p
PIVOT
(
COUNT(Quantity1) FOR Name1 IN ('
+ STUFF(REPLACE(@columns1, ', [', ',['), 1, 1, '')
+ ')
) AS Q
;';
EXEC(@SQL)
Regards,
Mitesh Oswal
+918698619998
Regards,
Mitesh OSwal
+918698619998
January 24, 2014 at 8:20 am
I would suggest that you take a look at dynamic cross tabs. Seems easier to work, especially when several columns and aggregates are involved.
January 24, 2014 at 8:52 am
Here's an example using CROSS TABS. It's shorter than the Pivot option, but you need to understand what the code is doing.
DECLARE @sql NVARCHAR(MAX)
SELECT @sql =STUFF((SELECT ',SUM( CASE WHEN p.ProductID = ' + CAST( p.ProductID AS varchar(10)) + ' THEN o.Quantity END) AS SUM_' + p.Name + CHAR(13) +
',AVG( CASE WHEN p.ProductID = ' + CAST( p.ProductID AS varchar(10)) + ' THEN o.Quantity END) AS AVG_' + p.Name + CHAR(13) +
',COUNT( CASE WHEN p.ProductID = ' + CAST( p.ProductID AS varchar(10)) + ' THEN o.Quantity END) AS COUNT_' + p.Name + CHAR(13)
FROM #Products p
FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)'),1,1,'SELECT ') + 'FROM #Products p
LEFT JOIN #OrderDetails o ON p.ProductID = o.ProductID';
PRINT @sql;
EXEC sp_executesql @sql;
January 25, 2014 at 7:15 am
Thank you so much Mitesh Oswal,
I did that way, but it was keep on showing error in my code. Out I want in different row. Is that possible.
Thanks,
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy