Rock的SQL筆記本

來源資料表

Stored Procedure Code

Create Procedure usp_Count
As
Declare @SQL Nvarchar(max);
Set @SQL='';
With tmpTB As(Select distinct ROW_NUMBER() Over(Partition By Name Order By Num) As Sno From product),
tmpTB2 As(Select Stuff((Select '],[' + Cast(Sno as Varchar) From tmpTB 
for xml path (''),type).value('.','VARCHAR(max)'),1,2,'')+']' As Col)
Select @SQL='SELECT 
Name,'+(Select Col From tmpTB2)+'
 FROM (
 SELECT Sno, Name, Num
  FROM (Select distinct ROW_NUMBER() Over(Partition By Name Order By Num) As Sno,* From product) As tmpTB3 
  GROUP BY Sno, Name, Num
  ) as GroupTable
  PIVOT
  (
   Sum(Num)
    FOR Sno IN ('+(Select Col From tmpTB2)+')
	) AS PivotTable';
Exec sp_executesql @SQL;

 

Stored Procedure執行結果

我是ROCK

rockchang@mails.fju.edu.tw