我有两个非常相似的情况,我需要生成SP.
在第一种情况下,我需要一个sql能够按活动数量返回列(如果可能,动态).我必须通过ActivityId创建列.
情况1：
Grouping by Date,count(ActivityId)
Returning columns: Activity1,Activity2,Activity3
表格1
╔════════════╦══════════════╗ ║ ActivityId ║ ActivityName ║ ╠════════════╬══════════════╣ ║ 1 ║ Activity 1 ║ ║ 2 ║ Activity 2 ║ ║ 3 ║ Activity 3 ║ ╚════════════╩══════════════╝
表2
╔═══════════╦════════════╗ ║ Date ║ ActivityId ║ ╠═══════════╬════════════╣ ║ 1/05/2015 ║ 1 ║ ║ 1/05/2015 ║ 1 ║ ║ 2/05/2015 ║ 2 ║ ║ 3/05/2015 ║ 3 ║ ╚═══════════╩════════════╝
查询结果
╔═══════════╦═══════════╦═══════════╦═══════════╗ ║ Date ║ Activity1 ║ Activity2 ║ Activity3 ║ ╠═══════════╬═══════════╬═══════════╬═══════════╣ ║ 1/05/2015 ║ 2 ║ 0 ║ 0 ║ ║ 2/05/2015 ║ 0 ║ 1 ║ 0 ║ ║ 3/05/2015 ║ 0 ║ 0 ║ 1 ║ ╚═══════════╩═══════════╩═══════════╩═══════════╝
案例2：
In another scenario I will have to do exactly the same thing but instead of
Activities it will be a list of days in a month:
表格1
╔════════════╦═══════════╦═══════╗ ║ Date ║ Account ║ Value ║ ╠════════════╬═══════════╬═══════╣ ║ 30/05/2015 ║ 1 ║ 10 ║ ║ 27/05/2015 ║ 2 ║ 40 ║ ╚════════════╩═══════════╩═══════╝
查询结果：
╔═════════╦════════════╦════════════╦════════════╦════════════╦═════════════════════╗ ║ Account ║ 30/05/2015 ║ 29/05/2015 ║ 28/05/2015 ║ 27/05/2015 ║…each day in a month ║ ╠═════════╬════════════╬════════════╬════════════╬════════════╬═════════════════════╣ ║ 1 ║ 10 ║ 0 ║ 0 ║ 0 ║ ║ ║ 2 ║ 0 ║ 0 ║ 0 ║ 40 ║ ║ ╚═════════╩════════════╩════════════╩════════════╩════════════╩═════════════════════╝
解决方法
对于案例1,试试这个：
--Creating Test tables
create table #activity
(
ActivityId TINYINT,ActivityName VARCHAR(20)
)
create table #date
(
[Date] DATE,ActivityId TINYINT
)
INSERT INTO #activity VALUES(1,'Activity 1')
INSERT INTO #activity VALUES(2,'Activity 2')
INSERT INTO #activity VALUES(3,'Activity 3')
INSERT INTO #date VALUES('2015-05-01',1)
INSERT INTO #date VALUES('2015-05-01',1)
INSERT INTO #date VALUES('2015-05-02',2)
INSERT INTO #date VALUES('2015-05-03',3)
DECLARE @activities NVARCHAR(MAX)
DECLARE @stmt NVARCHAR(MAX)
SET @activities = ''
SET @stmt = ''
--Get List of Activities
SELECT @activities = @activities + ',[' + ActivityName + ']'
FROM #activity
SET @activities = RIGHT(@activities,LEN(@activities)-1) --Remove Leading Comma
--Build PIVOT Statement
SET @stmt = 'SELECT [Date],' + @activities + '
FROM (SELECT d.[Date],a.ActivityName
FROM #date d
INNER JOIN #activity a ON d.ActivityId = a.ActivityId) tab
PIVOT (COUNT(ActivityName) FOR ActivityName IN (' + @activities + ')) AS NumberOfActivities'
--Execute
EXEC sp_executesql @stmt
--CleanUp
DROP TABLE #activity
DROP TABLE #date
