Dynamic Pivot - Test

0

Please login or register to vote for this query.

(click on this box to dismiss)

Bonjour

Meta Stack Exchange

Q&A for meta-discussion of the Stack Exchange family of Q&A websites

CREATE TABLE #Products
(
  ProductID INT PRIMARY KEY,
  Name      NVARCHAR(255) NOT NULL UNIQUE
  /* other columns */
);
INSERT #Products VALUES
(1, N'foo'),
(2, N'bar'),
(3, N'kin');
CREATE TABLE #OrderDetails
(
  OrderID INT,
  ProductID INT NOT NULL
    FOREIGN KEY REFERENCES #Products(ProductID),
  Quantity INT
  /* other columns */
);
INSERT #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 #Products AS p
  INNER JOIN #OrderDetails AS o
  ON p.ProductID = o.ProductID
  GROUP BY p.Name) AS x;
  
select @columns
  
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
  SELECT p.Name, o.Quantity
   FROM #Products AS p
   INNER JOIN #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;

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results