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.


Comments

Post a Comment

Popular posts from this blog

Django REST Framework perform_create: You cannot call `.save()` after accessing `serializer.data` -

-
i have following viewset: class artistprofileviewset(viewsets.modelviewset): queryset = artistprofile.objects.all() serializer_class = artistprofileserializer def perform_create(self, serializer): if serializer.is_valid(): serializer.save() with following serializers: class simpleartisttrackserializer(serializers.hyperlinkedmodelserializer): class meta: model = artisttrack fields = (...my fields...) class artistprofileserializer(serializers.hyperlinkedmodelserializer): owners = userserializer(many=true, required=false) tracks = simpleartisttrackserializer(many=true, required=false) class meta: model = artistprofile fields = (...my fields...) i getting following error: assertionerror: cannot call `.save()` after accessing `serializer.data`.if need access data before committing database inspect 'serializer.validated_data' instead. i don't see editing serializer.data object. th...
Read more

Why does Go error when trying to marshal this JSON? -

-
i'm trying rather simple example of trying decode json file structs using golang. however, when attempting error that json: cannot unmarshal object go value of type []main.track which don't understand. here's code in question, , json can found here . package main import ( "encoding/json" "fmt" "net/http" "os" ) // tracks group of track types type tracks struct { tracks []track } // track represents singular track type track struct { name string url string artist artist } // artist represents single artist type artist struct { name string mbid string url string } func main() { url := "http://ws.audioscrobbler.com/2.0/?method=geo.gettoptracks&api_key=c1572082105bd40d247836b5c1819623&format=json&country=netherlands" response, err := http.get(url) if err != nil { fmt.printf("%s\n", err) os.exit(1) } de...
Read more