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.

  • 2
    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?
    – Andriy M
    May 31, 2016 at 6:30
  • 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) May 31, 2016 at 10:07
  • i think its table structure May 31, 2016 at 10:09

1 Answer 1


Simple Group by will work for your expected output

max(Value1) as value1, max(User1)as user1 , max(Value2) as value2,  max(User2) as user2,max(Value3) as value3,max(User3) as user3
group by
  • 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.
    – Mike
    May 31, 2016 at 14:08
  • I have clarified that as "for your expected output" May 31, 2016 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.