Combining data from columns in one row

Question

I am trying to combine partial contents of rows that are the result set of a query from SQL Server 2005 that reads .CSV. Here is a simplified version of the data that I have:

objectID | value1 | value2 _________________________________ 12 | R | 100 12 | R | 101 12 | S | 220 13 | D | 88 14 | K | 151 14 | K | 152 

What I'm trying to get is a grouping of each objectID value in one row, so for each object identifier there is one and only one row. In graphic terms:

 objectID | value1a | value2a | value 1b | value2b | value1c | value2c ______________________________________________________________________________ 12 | R | 100 | R | 101 | S | 220 13 | D | 88 | | | | 14 | K | 151 | K | 152 | | 

Empty cells are empty.

I was hoping to do this in Excel or Access without VB, but CONCAT and other similar functions (and the answers here and elsewhere offering similar approaches) do not work, because each value should remain in its own cell (this data will eventually be combined with the Word form). If the answer is a stored procedure or SQL cursor, this is normal, although I am not very effective at writing them yet.

Thanks to everyone.

Answer 1

First import the data into the temp table. Something like this data will appear in the temp table:

 create table #tmp (objectID int, value1 char(1), value2 int) insert #tmp select 12 ,'R', 100 union all select 12 ,'R', 101 union all select 12 ,'S', 220 union all select 13 ,'D', 88 union all select 14 ,'K', 151 union all select 14 ,'K', 152 

Then you can use this SQL batch, which you can put in the stored procedure, if necessary.

 declare @sql nvarchar(max) select @sql = ISNULL(@sql+',','') + 'max(case when rn=' + cast(number as varchar) + ' then value1 end) value' + cast(number as varchar) + 'a,' + 'max(case when rn=' + cast(number as varchar) + ' then value2 end) value' + cast(number as varchar) + 'b' from master..spt_values where type='P' and number between 1 and ( select top 1 COUNT(*) from #tmp group by objectID order by 1 desc) set @sql = ' select objectID, ' + @sql + ' from ( select rn=ROW_NUMBER() over (partition by objectID order by value2), * from #tmp) p group by ObjectID' exec (@sql) 

Output

 objectID value1a value1b value2a value2b value3a value3b ----------- ------- ----------- ------- ----------- ------- ----------- 12 R 100 R 101 S 220 13 D 88 NULL NULL NULL NULL 14 K 151 K 152 NULL NULL Warning: Null value is eliminated by an aggregate or other SET operation.