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.

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