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 '16 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) – Arun Prasad E S May 31 '16 at 10:07
  • i think its table structure – Arun Prasad E S May 31 '16 at 10:09

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
| improve this answer | |
  • 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 '16 at 14:08
  • I have clarified that as "for your expected output" – TheGameiswar May 31 '16 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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