如何从SQL Server中的分组值返回动态列(存储过程)

我有两个非常相似的情况,我需要生成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