将单个表的多个SQL查询合并到单独的列中

Question

想要解释我正在尝试做什么的标题已经很艰难了。我一直在使用Google搜索一段时间，但没有找到任何地方。

使用SQL Server 2008 R2企业

我试图让多个结果（列）针对单个表的查询。该表看起来是这样的：

sample_id sampletime    samplevalue 
100  2013-09-07 00:00:00.000 12 
101  2013-09-07 00:00:00.000 15 
102  2013-09-07 00:00:00.000 11 
100  2013-09-07 00:05:00.000 14 
101  2013-09-07 00:05:00.000 12 
102  2013-09-07 00:05:00.000 13 

我想要做的就是一个平均每人每天/每周/等sample_id但各有sample_id是列。我现在有这个疑问：

select 
    DATEDIFF(ww, GETDATE(), sampletime) AS weeks_ago, 
    AVG(samplevalue) AS item1 
from table 
where 
    sample_id = '100' 
    and sampletime between '2013-09-01' AND '2013-10-01' 
group by DATEDIFF(ww,getdate(),sampletime) 
order by weeks_ago 

这给了我下面的输出：

weeks_ago item1 
-5   10.717936 
-4   13.009690 
-3   11.401884 
-2   11.073626 
-1   15.417648 
0   18.399488 

这正是我所要的输出看，但是问题是，我需要做的这个查询几十个不同sample_id的，我真的很想得到的输出看起来是这样的：用“IN”和包括

weeks_ago item1  item2  item3 
-5   10.717936 11.401884 6.944170 
-4   13.009690 10.717936 8.330120 
-3   11.401884 18.399488 7.476393 
-2   11.073626 15.417648 7.933386 
-1   15.417648 13.009690 9.651132 
0   18.399488 18.399488 7.456417 

等等...... 我已经试过一堆sample_id的像这样：

select 
    sample_id, 
    DATEDIFF(ww, GETDATE(), sampletime) AS weeks_ago, 
    AVG(samplevalue) AS avg_vol 
from table 
where 
    sample_id in ('100','101','102') 
    and sampletime between '2013-09-01' AND '2013-10-01' 
group by DATEDIFF(ww,getdate(),sampletime), sample_id 
order by weeks_ago 

但是，这给我的输出是这样的：

sample_id avg_vol weeks_ago 
100  6.834470 -4 
101  3.235943 -4 
102  3.952023 -4 
100  10.330120 -3 
101  4.753588 -3 
102  3.928382 -3 
100  1.401884 -2 
101  7.476393 -2 
102  6.426609 -2 

这不是伟大的，因为它没有明确说明如何在平均交易量已经改变了一段时间的具体项目..不知道我是否做了很好的解释问题是什么，但如果有人有任何建议，我非常感激！

解决！

SELECT weeks_ago, [100] as item1, [101] as item2, [102] as item3, [n..] 
FROM (
    SELECT 
    sample_id, 
    DATEDIFF(ww, GETDATE(), sampletime) as weeks_ago, 
    samplevalue 
    FROM table 
    WHERE sample_id in (100,101,102,n...) 
    AND sampletime between 'YYYY-MM-DD' and 'YYYY-MM-DD' 
) main 
PIVOT (
    AVG(samplevalue) for sample_id in ([100],[101],[102],[n..]) 
) pvt 

谢谢大家的帮助！

Answer 1

正如Sean Lange在上面的评论中提到的那样，这听起来像是你想对你的数据做一个Pivot，这样你就可以得到每件物品。

使用上面的表率：

select weeks_ago, pvt.[100], pvt.[101], pvt.[102] 
FROM (
    select 
     sample_id, 
     DATEDIFF(ww, GETDATE(), sampletime) AS weeks_ago, 
     samplevalue 
    from #sample 
) main 
PIVOT 
(
    AVG(samplevalue) FOR sample_id in ([100], [101], [102]) 
) pvt 

以透视的缺点是，除非你动态生成这个查询，你必须知道你所有的样本时间提前，这可能会很麻烦的。动态生成的

例子：

DECLARE @string nvarchar(max) = '', @sql nvarchar(max) = ''; 

select @string = 
    (
     select distinct '[' + cast(sample_id as varchar(5)) + '],' from #sample FOR XML PATH('') 
    ) 

select @string = LEFT(@string, LEN(@string)-1) 

select @string 

SELECT @sql = 
' 
select weeks_ago, '[email protected]+' 
FROM (
    select 
     sample_id, 
     DATEDIFF(ww, GETDATE(), sampletime) AS weeks_ago, 
     samplevalue 
    from #sample 
) main 
PIVOT 
(
    AVG(samplevalue) FOR sample_id in ('[email protected]+') 
) pvt 
' 

EXEC (@sql); 

使用动态SQL可能会非常棘手，不过，如果你不习惯使用它，我不建议使用它，或者如果你调用代码由于SQL注入等的危险而直接（而不是说存储过程）。

Answer 2

我对SQL服务器并不十分熟悉，但是经过Google搜索之后，我发现了一个可能有效的解决方案。

首先，简化一切，创建一个临时表：

select 
    sample_id, 
    DATEDIFF(ww, GETDATE(), sampletime) AS weeks_ago, 
    AVG(samplevalue) AS avg_vol 
into #temp_table 
from table 
where 
    sample_id in ('100','101','102') 
    and sampletime between '2013-09-01' AND '2013-10-01' 
group by DATEDIFF(ww,getdate(),sampletime), sample_id 
order by weeks_ago; 

现在用#temp_table工作。首先，让我们独特的“sample_id`值：

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX); 
DECLARE @ColumnName AS NVARCHAR(MAX); 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
     + QUOTENAME(sample_id) 
FROM (SELECT DISTINCT sample_id FROM #temp_table) AS t; 

现在，让我们来构建数据透视表：

--Prepare the PIVOT query using the dynamic 
SET @DynamicPivotQuery = 
    N'SELECT week_ago, ' + @ColumnName + ' 
    FROM #temp_table 
    PIVOT(SUM(avg_vol) 
      FOR week_ago IN (' + @ColumnName + ')) AS PVTTable' 
--Execute the Dynamic Pivot Query 
EXEC sp_executesql @DynamicPivotQuery; 

希望这有助于。

---

参考：

• Dynamic Pivot in Sql Server