sql - RETURN comma list with NULLs if item not found IN list -

i running microsoft sql server 2012 - 11.0.5058.0 (x64) standard edition (64-bit) on windows nt 6.3 (build 9600: ) (hypervisor). inputing sensor data database @ 1hz. each test has variable number of sensors of type. channel name (chname) describe user are.

the user select testid , chname's want web interface.

i have table setup contains approximately 15 million rows ~50 testids:

timestamp datetime, testid int, chname varchar(100), value real

timestamp | testid | chname | value 13:52:12  | 1000   |      | 23 13:52:12  | 1000   | b      | 2 13:52:12  | 1000   | c      | 150 13:52:13  | 1000   |      | 25 13:52:13  | 1000   | c      | 147 13:52:13  | 1000   | b      | 1 13:52:14  | 1000   |      | 24 13:52:14  | 1000   | b      | 4 13:52:14  | 1000   | c      | 151 13:52:15  | 1000   | b      | 8 13:52:15  | 1000   | c      | 153 13:52:16  | 1000   | b      | 3 13:52:16  | 1000   | c      | 149 13:52:17  | 1000   | c      | 152 13:52:17  | 1000   |      | 27 

i looking query when searching specific testid , specific chname's return comma separated result in order searched nulls not found.

for example searching testid 1000 , chnames ('a','b','c') return:

timestamp  | data 13:52:12   | 23,2,150 13:52:13   | 25,1,147 13:52:14   | 24,4,151 13:52:15   | null,8,153 13:52:16   | null,3,149 13:52:17   | 27,null,152 

searching testid 1000 , chnames ('b','c') return:

timestamp  | data 13:52:12   | 2,150 13:52:13   | 1,147 13:52:14   | 4,151 13:52:15   | 8,153 13:52:16   | 3,149 13:52:17   | null,152 

i've implemented in php returning rows contain testid , chname's slow (returning ~503,000 rows , doing grouping in php takes approximately 2 minutes). believe table structured better unfortunately inherited design trying more efficient query.

purpose of data pull , export excel or user can graph via webapp. user has ability select data or time period.

the query when requesting data looks , in php group them , add null if not found.

select timestamp,chname,value data_table testid=1000 , chname in ('a','b',c') order timestamp,chname 

i suggest use pivot , build query dynamically based on list of values chname want produce result for:

select timestamp,         coalesce([a], 'null') + ',' +         coalesce([b], 'null') + ',' +         coalesce([c], 'null') data     (   select timestamp, chname, value            data_table          testid = 1000         ,    chname in ('a',b',c')     ) sourcetable pivot     (         max(value)         chname in ([a], [b], [c])     ) pivottable; 

i have not tested this, may have syntax issues.