Combining multiple-row data into a single row

Question

I have a table that has columns like following

ID Number Value1 User1 Value2 User2 Value3 User3
-- ------ ------ ----- ------ ----- ------ ------
1 123 0 Jack
1 123 1 TOM
1 123 2 Tim
1 456 0 Jones
1 456 1 Jim
1 456 2 Carter

I need result like the following,

ID Number Value1 User1 Value2 User2 Value3 User3
-- ------ ------ ----- ------ ----- ------ ------
1 123 0 Jack 1 TOM 2 Tim
1 456 0 Jones 1 Jim 2 Carter

Can anyone please help me out on this?

The column ID remains same. The columns Value and User will go till Value6 and User6 and each row has values like one below the other. I need to combine all of them and show it as a single row.

• Andriy M: Is this really a table or is this the result of a query? Would it be possible (and wiser) to fix that query? If not, what are the blank bits? Are they nulls or are they spaces/empty strings?
• Arun Prasad E S: Look like you have value in all fields, you can do it in PHP or ASP by just iterating through the collection and checking if value is present , then save those, if you want to do it faster, use T-Sql (tell me if you need help)
• Arun Prasad E S: i think its table structure

Answer 1

Simple Group by will work for your expected output

select 
columnid,
Number,
max(Value1) as value1, max(User1)as user1 , max(Value2) as value2, max(User2) as user2,max(Value3) as value3,max(User3) as user3
from 
table
group by
columnid,
number

• Mike: This will work nicely as long as there is only one value in each column within a grouping - and that is what the example data shows. However if the source data ever has multiple values for the same column within a group, the MAX statements will not necessarily get the values that @Nithin is hoping for.
• TheGameiswar: I have clarified that as "for your expected output"