Combining multiple SQL queries of a single table into separate columns

Question

Had a tough time just coming up with a title that explains what I'm trying to do.. I've been googling for a while and am not getting anywhere.

Using SQL Server 2008r2 Enterprise

I'm trying to get multiple results (columns) for queries against a single table. The table looks something like this:

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

What I'd like to do is get a averages per sample_id per day/week/etc but have each sample_id be a column. I currently have this query:

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

That gives me the following output:

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

That's exactly how I want the output to look, however the issue is that I need to do this query for dozens of different sample_id's, and I'd really like to get the output to look something like this:

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

And so on... I've tried using 'IN' and including a bunch of sample_id's like so:

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

But that gives me output like this:

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

That's not great because it doesn't make it clear how the avg volume has changed for specific items over time.. Not sure if I'm doing a good job of explaining what the problem is but if anyone has any suggestions I'd very much appreciate it!

Solved!

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

Thank you everyone for your help!

How about some ddl and sample data? Without that we are guessing at best. sqlfiddle.com is a great place to get started. — Sean Lange, Sep 12 '14 at 18:53
Sean: In theory I would think the first block showing 6 lines from the table should be enough, no? But I'll take a look at sqlfiddle. Horaciux: added server info to the description. It's 2k8r2 EE. — Martin, Sep 12 '14 at 19:09
If I understand correctly, you are trying to get a column for each `sample_id` value. Is that what you need? If so, then you need a pivot table. Take a look to [this](http://sqlhints.com/2014/03/10/pivot-and-unpivot-in-sql-server/) and [this](http://sqlhints.com/2014/03/18/dynamic-pivot-in-sql-server/) — Barranka, Sep 12 '14 at 19:13
Are these sample ids known in advance and are in limited number or new sample ids might come up at later time? — radar, Sep 12 '14 at 19:16
Except your 6 lines are not anything I can put into a table without extra work. Also, that small amount of sample data doesn't come close to your expected output. There is nothing like an item1, item2 etc. If your query is getting the desired results for a single item then you need to either PIVOT or use a cross tab to generate the extra columns — Sean Lange, Sep 12 '14 at 19:16
Take a look at this question. http://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query — Sean Lange, Sep 12 '14 at 19:28
Barranka: Correct. Rajesh: Yes they are, I've got a list of about 30 specific sample_id's that I need to get this data for. — Martin, Sep 12 '14 at 20:06

score 2 · Accepted Answer · answered Sep 12 '14 at 19:32

As Sean Lange mentions in a comment above, it sounds like you want to do a Pivot on your data so that you can get each item.

An example using your set above:

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

The disadvantage to Pivot is that, unless you generate this query dynamically, you have to know all of your samples ahead of time, which could get tedious.

Example of dynamic generation:

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, '+@string+'
FROM (
    select
      sample_id,
      DATEDIFF(ww, GETDATE(), sampletime) AS weeks_ago,
      samplevalue
    from #sample
) main 
PIVOT
(
    AVG(samplevalue) FOR sample_id in ('+@string+')
) pvt 
'

EXEC (@sql);

Using dynamic SQL can be tricky, though, and I don't recommend using it if you're not used to using it, or if you're calling code directly (rather than say, a stored procedure) because of the dangers of SQL injection and the like.

The sample_id's are known ahead of time so I don't need to do any dynamic queries. SQL is giving me an error at pvt.[100] saying "The multi-part identified pvt.100 could not be bound" — Martin, Sep 12 '14 at 20:08
@Martin Leave off the `pvt.` if needed, but do be sure to have the brackets, of course. Here's a SQL Fiddle of the example, as well, as a test: http://sqlfiddle.com/#!3/af81e/1 — Jason Whitish, Sep 12 '14 at 20:15
That worked! One more thing now: is there a way I can change the column headers? So instead of showing "100", "101", etc, I'd like to name them specifically, for example id 100 should be called "p4_a1" (which is a static name that's not anywhere in the table).. — Martin, Sep 12 '14 at 20:46
Figured it out, just used "as" in the select statement. Thank you very much Jason! — Martin, Sep 12 '14 at 20:53

score 0 · Answer 2 · answered Sep 12 '14 at 19:34

I'm not quite familiar with SQL server, but after googling around, I found a solution that might work.

First, to simplify everything, create a temporary table:

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;

Now work with #temp_table. First, let's get the unique 'sample_id` values:

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;

And now, let's build the pivot table:

--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;

Hope this helps.

Reference:

Dynamic Pivot in Sql Server

Getting error "Invalid column name 'avg_vol'" and "invalid column name 'weeks_ago'" when I try the 3rd step, although when I do a select * from #temp_table I see that the columns headers are in fact called "weeks_ago" and "avg_vol".. — Martin, Sep 12 '14 at 20:39

Combining multiple SQL queries of a single table into separate columns

2 Answers2