Combine multiple rows using SQL Server 2008

Asked By jasmine collins 330 points N/A Posted on - 10/21/2011

I am using SQL Server 2008. I have provided an image of the output that I intended to make. I want to combine multiple rows and make a single ID, while bringing the Non-Null columns into whole or consolidate them. Please look at the rows and columns below:

ID   X     Y     Z

 

1    1     NULL  NULL

 

1    NULL  1     NULL

 

NULL  NULL  1

 

2    1     NULL  NULL

 

2    NULL  1     NULL

 

2    NULL  NULL  1

 

3    1     NULL  NULL

 

3    1     1     NULL

 

What I want to return:

 

ID   X     Y     Z

 

1    1     1     1

 

2    1     1     1

 

3    1     1     NULL

• Status: Open
• Question Views: 1166
• Answer Count: 1
• Vote Up 0 Vote Down

• Answer Accepted: No
• Question Category: MySQL Server

Answered By Jackman 0 points N/A #107755

Combine multiple rows using SQL Server 2008

Select the IDs as names that is ID 1 name 1, ID 2 name 2 and ID 3 name 3. Select ID that is (name) partitioning by ID ordering by name, then to create some thing like ID X Y Z ; then create this in the main query and whenever you need an additional one just add the number in the query.

The other option of doing this, is to use a PIVOT operation on with a Row or Rank function. The other way of doing it is to concatenate all the data together and use a comma to separate the list or even return the names as XMLs. You can try all this and see which one works best for you, then apply the one that works for you.

I hope this would be of use to you, in combining the rows you want and make your work easy.

About Jackman

Questions
4

Answers
159

Best Answers
47

• Vote Up 0 Vote Down

• Posted on - 10/25/2011
• Question Category: MySQL Server