Combine multiple rows using SQL Server 2008

Asked By 330 points N/A Posted on -
qa-featured

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
SHARE
Answered By 0 points N/A #107755

Combine multiple rows using SQL Server 2008

qa-featured

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.

Related Questions