February 5, 2009 at 12:23 pm
Hi all -
I'm not sure if this makes sense or not, but I have a query that returns four different records with basically the same information, except for a couple of columns that are different because they have some extra information in them. Is there an easy way to combine all four records into one? Or would an update statement be the way to go?
Example:
Before -
Column 1 Column 2 Column 3 Column 4 Column 5
name aaa
name bbb
name ccc
name ddd
After -
Column 1 Column 2 Column 3 Column 4 Column 5
name aaa bbb ccc ddd
Thanks for your help in advance.
February 5, 2009 at 12:58 pm
I have an answer IF a given row in the "before" table will never contain information in more than one column. Answer yes or no whether the scenario below could ever happen, assuming that "name" is the same value in column 1 for all rows.
Column 1 Column 2 Column 3 Column 4 Column 5
Smith aaa
Smith bbb
Smith ccc
Smith ZZZ ddd
If the answer is NO, then you can do it with a summary query grouped on column 1, and using the MAX() function to get the values for Columns 2-5 on one row. Like this:
-- set up test table and populate with sample data
declare @Before table (column1 varchar(10), column2 varchar(10), column3 varchar(10), column4 varchar(10), column5 varchar(10))
insert into @before
select 'Smith','AAA','','','' union all
select 'Smith','','BBB','','' union all
select 'Smith','','','CCC','' union all
select 'Smith','','','','DDD'
select * from @before
-- take max values from column2-column5, grouping by column1
select column1, max(column2)[column2], max(column3) [column3], max(column4) [column4], max(column5) [column1]
from @before
group by column1
__________________________________________________
Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply