use sql selected values as row names for another select

Question

On a MSSQL server, given the tables:

TABLE values {
    int id;
    timestamp date;
    int value;
}

TABLE value_type {
    int value_id; // foreign key on values.id
    text name;
}

I can issue a query like:

SELECT date, name, value
FROM values
LEFT JOIN value_type ON value_id = id;

and get something like:

+----------+------+-------+
| date     | name | value |
+----------+------+-------+
| 01.01.10 | foo  |  1.0  |
+----------+------+-------+
| 01.01.10 | bar  |  2.0  |
+----------+------+-------+
| 02.01.10 | bar  |  4.0  |
+----------+------+-------+
| 03.01.10 | foo  |  5.0  |
+----------+------+-------+

What's the most efficient way to query the database if I'd like to get the data like this?

+----------+------+-------+
| date     | foo  | bar   |
+----------+------+-------+
| 01.01.10 | 1.0  |  2.0  |
+----------+------+-------+
| 02.01.10 | NULL |  4.0  |
+----------+------+-------+
| 03.01.10 | 5.0  |  NULL |
+----------+------+-------+

The database structure is a lot more complicated, and unfortunately I can't change it.

EDIT: The solution should not use foo and bar as hardcoded values in the query.

Answer 1

For SQL Server 2005+, you can use dynamic SQL and PIVOT.

DECLARE @ValuesNames NVARCHAR(4000), @Query NVARCHAR(MAX)
SET @ValuesNames = ''

SELECT @ValuesNames = @ValuesNames + QUOTENAME([name]) + ','
FROM [values] A
INNER JOIN value_type B
ON B.value_id = A.id
GROUP BY [name]
ORDER BY [name]

SET @ValuesNames = LEFT(@ValuesNames,LEN(@ValuesNames)-1)

SET @Query = '
SELECT [date], '+@ValuesNames+'
FROM (  SELECT [date], [name], value
        FROM [values]
        LEFT JOIN value_type ON value_id = id) A
PIVOT(SUM(value) FOR [name] IN ('+@ValuesNames+')) AS PT
'
EXEC sp_executesql @Query

Answer 2

You could group by on Date:

SELECT  Date
,       max(case when vt.name = 'foo' then v.value end) as Foo
,       max(case when vt.name = 'bar' then v.value end) as Bar
FROM Values v
LEFT JOIN 
        value_type vt
ON      vt.value_id = v.id
group by
        Date

By the way, the foreign key seems unusual. I'd expect a value_type column in the values table, not a value_id column in the values_type table! There are a lot more values than value types, right?

EDIT: There is no way in SQL to generate a variable number of columns, so you'll have to resort to meta-SQL, or dynamic SQL. Here's an example:

create table [values] (id int, date datetime, value float)
insert [values] values (1, '2010-01-01', 1.0)
insert [values] values (2, '2010-01-01', 2.0)
insert [values] values (3, '2010-01-02', 4.0)
insert [values] values (4, '2010-01-03', 5.0)
create table value_types (value_id int, name varchar(max))
insert value_types values (1,'foo'), (2,'bar'), (3,'bar'), (4,'foo')

declare @sql varchar(max)
set @sql = ''
select  @sql = @sql + ',       max(case when vt.name = ''' + 
                   name + ''' then v.value end) as ' + name + CHAR(13) + CHAR(10)
from    value_types
group by
        name

set @sql = 'SELECT  Date
' + @sql + 
'FROM   [values] v
LEFT JOIN 
        value_types vt
ON      vt.value_id = v.id
group by
        Date'

exec (@sql)

This prints:

Date        bar     foo
----------- ------- -------
2010-01-01  2       1
2010-01-02  4       NULL
2010-01-03  NULL    5