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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply