sql - Merge multiple records for the same foreign key -


the source pivoted table stored proc. know column names first 3 columns, remainder user defined pivot , don't know names ahead of time. here's example:

create table table1     ([id] int, [reportid] int, [fieldid] int, [col1] varchar(3), [col2] int, [col3] varchar(3));  insert table1     ([id], [reportid], [fieldid], [col1], [col2], [col3]) values     (1, 2, 1, 'abc', null, null),     (2, 2, 2, null, 123, null),     (3, 2, 3, null, null, 'a3a')     (4, 3, 1, null, null, null),     (5, 3, 2, null, 456, null),     (6, 3, 3, 'def', null, null);

the desired output group data reportid, replacing null values non-null values when exist in other rows of same reportid. id column product of pivot , unused. fieldid column relates rest of columns.

(1, 2, 1, 'abc', 123, a3a) (4, 3, 1, 'def', 456, null)

i looked @ using cursor worried scaling.

i looked @ using merge, don't know column names, cannot write columnname in place.

we have access text list of user defined column names querying table.

any ideas?

you can create dynamic sql using sys.columns system view follows

    create table table1     ([id] int, [reportid] int, [fieldid] int, [col1] varchar(3), [col2] int, [col3] varchar(3));  insert table1     ([id], [reportid], [fieldid], [col1], [col2], [col3]) values     (1, 2, 1, 'abc', null, null),     (2, 2, 2, null, 123, null),     (3, 2, 3, null, null, 'a3a'),     (4, 3, 1, null, null, null),     (5, 3, 2, null, 456, null),     (6, 3, 3, 'def', null, null);   declare @sql varchar(max)='select min(id) id, reportid, min(fieldid) fieldid '   select @sql+=',min('+c.name+') '+c.name sys.columns c  object_id=object_id(n'table1')         , c.name not in ('id','reportid','fieldid')    select @sql+=' table1 group reportid'   exec (@sql)   drop table table1

but in case may need grant select permission on sys.columns in master database.

i think, better group data in query table created.


Comments

Post a Comment

Popular posts from this blog

get url and add instance to a model with prefilled foreign key :django admin -

Image
i have page in each contest shown in row. each row contains contest id , link other model names have foreign key contest. news model: class news(models.model): title = models.charfield(max_length=50, null=true) description = models.textfield() time = models.timefield( default= datetime.datetime.now().time()) contest = models.foreignkey(contest) i want use admin adding , changing these models. want when user clicks on 'news' has foreign key contest id=1, shows list of news value foreign key. use {% instance in instances %} <tr class="{% cycle 'row1' 'row2' %}"> <td>{{ instance.title }}</td> <td>{{ instance.starttime }}</td> <td>{{ instance.timespan }}</td> <td>{{ instance.date }}</td> <td><a href="/adm...
Read more

android - Keyboard hides my half of edit-text and button below it even in scroll view -

i having trouble keyboard covers edit-text , half of button in scroll view. here's layout , please tell me how should resolve problem. many questions on topic none of them s working me. <?xml version="1.0" encoding="utf-8"?> <linearlayout xmlns:android="http://schemas.android.com/apk/res/android" android:layout_width="match_parent" android:background="@drawable/bg" android:layout_height="match_parent"> <scrollview android:layout_width="match_parent" android:fillviewport="true" android:layout_height="match_parent"> <relativelayout android:layout_width="match_parent" android:layout_height="wrap_content" android:orientation="vertical"> <linearlayout android:layout_width="match_parent" android:layout_height="w...
Read more

css - Make div keyboard-scrollable in jQuery Mobile? -

from can tell, although jquery-mobile-powered pages can contain divs overflow set scroll or auto, , these divs can scrolled one-screen bar or mouse wheel, cannot scrolled using arrow keys, page-up/page-down, or home/end. instead, official "page" div (with data-role="page") absorbs input. perhaps other divs can't acquire focus, i'm not sure. is there way around this? edit: jsfiddle of simple example: https://jsfiddle.net/qogz0shx/ <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.4/jquery.min.js"></script> <script src="https://ajax.googleapis.com/ajax/libs/jquerymobile/1.4.5/jquery.mobile.min.js"> </script> <link rel="stylesheet" href="https://code.jquery.com/mobile/1.4.5/jquery.mobile-1.4.5.css"/> <style> #outer { overflow:scroll; height: 50vh; width: 50vw; } #inner { height: 500vh; width: 500vw; } ...
Read more