Merge multiple records for the same foreign key

Question

The source is a pivoted table from a stored proc. We know the column names for the first three columns, but the remainder are user defined from the pivot and we don't know the names ahead of time. Here's an example:

CREATE TABLE Table1
    ([ID] int, [ReportID] int, [FieldID] int, [Col1] varchar(3), [Col2] int, [Col3] varchar(3));

INSERT INTO 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 is to group the data by ReportID, replacing NULL values with non-NULL values when they exist in other rows of the same ReportID. The ID column is a product of the pivot and is unused. The FieldID column relates to the rest of the columns.

(1, 2, 1, 'abc', 123, A3A)
(4, 3, 1, 'def', 456, NULL)

I looked at using a cursor but worried about it scaling.

I looked at using MERGE, but don't know the column names, so cannot write it with the columnname in place.

We do have access to a text list of the user defined column names by querying another table.

Any ideas?

When you say you don't know the column name, can't you use desc tablename to find the column names? — Izaaz Yunus, Mar 14 '14 at 01:22

score 0 · Accepted Answer · answered Mar 14 '14 at 01:22

0

You can create dynamic sql using sys.columns system view as follows

    CREATE TABLE Table1
    ([ID] int, [ReportID] int, [FieldID] int, [Col1] varchar(3), [Col2] int, [Col3] varchar(3));

INSERT INTO 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 from sys.columns c
 where object_id=object_id(N'table1')
        and c.name not in ('ID','ReportID','FieldID')


 select @sql+=' from table1 group by ReportID'

 exec (@sql)

 drop table Table1

But in this case you may need to grant select permission on sys.columns in master database.

I think, it is better to group your data in the query where the table is created.

answered Mar 14 '14 at 01:22

Igor Borisenko

3,806
3
34
49

Since the OP also had a concern with respect to performance it may be helpful to include a discussion on the difference between an explicit `EXEC` command and a call using the dynamic sql statement processed through the command: `sp_executesql`. With the latter the RDBMS retains a knowledge of similar dynamic sql calls so that it can reuse those execution plans for faster completion. A brief discussion of the difference is [here](http://sqlmag.com/database-performance-tuning/don-t-fear-dynamic-sql) in an article from SQLMag.com. – Richard Pascual Mar 14 '14 at 02:06
Script you provided works exactly the way I need it. The actual source comes from a stored procedure (exec [spname]) and is pulled into a temp table. When I change "table1" in your script to the temp table, I only get the ID column in the results. Is there a different place to use for sys.columns for temp tables? – Tremour101 Mar 14 '14 at 03:20
@Tremour101 It is well described here: http://stackoverflow.com/questions/756080/how-to-retrieve-field-names-from-temporary-table-sql-server-2008 – Igor Borisenko Mar 14 '14 at 03:27
For the #temp table: tempdb.sys.columns, and the where clause is now where object_id=object_id(N'tempdb..#temp') – Tremour101 Mar 14 '14 at 03:30
OK, looks like it's now just hanging up on invalid character names in the fields: "Incorrect syntax near 'Arrival'." for the column name [Actual Arrival Airport] (the first user defined column. In my example, imagine [col1] being [Actual Arrival Airport] instead. I added [] to select @sql+=',MIN(['+c.name+']) '+c.name from tempdb.sys.columns c – Tremour101 Mar 14 '14 at 03:33
Here we go: select @sql+=',MIN(['+c.name+']) ['+c.name + ']' from tempdb.sys.columns c Awesome. :) – Tremour101 Mar 14 '14 at 03:37

Merge multiple records for the same foreign key

1 Answers1