Use sql selected values ​​as row names for another choice

Question

On the 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 ask 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 is the most efficient way to query a database if I would like to get such data?

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

The structure of the database is much more complicated, and, unfortunately, I can not change it.

EDIT . The solution should not be used fooand baras a hard-coded value in the request.

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 can group byby 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

, . value_type values, value_id values_type! , , ?

EDIT: SQL , -SQL SQL. :

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)

:

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

Answer 3

: SQL- , , .

Answer 4

untested: , pk-fk, - :

Select date, 

case name when "foo" then value else null end as foo, 

case name when "base" then value else null end as bar 

from mytable  

order by date

.

To make it more flexible, you can build a dynamic case statement from various "name" values ​​in a stored procedure and call exec () on this line.