January 18, 2011 at 9:52 pm
Hi T-Sql gurus,
Guys I have question,
Below is the sample table
ID NAME STATE E.MAIL
1 JAMES NULL NULL
1 NULL CA NULL
1 NULL NULL abc@hotmail.com
I want result like this
ID NAME STATE E.MAIL
1 JAMES CA abc@hotmail.com
Please let me know how I can accomplish this kind of problem.
Thanks in advance.
January 18, 2011 at 10:23 pm
Rather than joining rows, why not build a better table design?
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
January 18, 2011 at 10:32 pm
Looks like home work, but I will oblige. Have not been here for a long while.
Please post queries using format in my signature.
create table #foo(id int, name varchar(200), [state] varchar(90), [e.mail] varchar(450))
insert into #foo
SELECT 1, 'JAMES', NULL, NULL UNION
SELECT 1, NULL, 'CA', NULL UNION
SELECT 1, NULL, NULL, 'abc@hotmail.com' UNION
SELECT 2, 'CAMERON', NULL, NULL UNION
SELECT 2, NULL, 'IL', NULL UNION
SELECT 2, NULL, NULL, 'xyz@hotmail.com'
SELECT ID, MAX(name), MAX([state]), MAX([e.mail])
FROM #foo
GROUP BY ID
DROP Table #foo
January 19, 2011 at 12:16 am
Just for giggles (plus I need to keep SQL Server fresh in my mind):
create table #foo(id int, name varchar(200), [state] varchar(90), varchar(450))
insert into #foo
SELECT 1, 'JAMES', NULL, NULL UNION
SELECT 1, NULL, 'CA', NULL UNION
SELECT 1, NULL, NULL, 'abc@hotmail.com' UNION
SELECT 2, 'CAMERON', NULL, NULL UNION
SELECT 2, NULL, 'IL', NULL UNION
SELECT 2, NULL, NULL, 'xyz@hotmail.com';
with Names as (
select
id,
name
from
#foo
where
name is not null
)
,States as (
select
id,
state
from
#foo
where
state is not null
)
,EMails as (
select
id,
from
#foo
where
email is not null
)
select
a.name,
b.state,
c.email
from
Names a
inner join States b
on (a.id = b.id)
inner join EMails c
on (a.id = c.id)
order by
a.id;
DROP Table #foo
January 20, 2011 at 3:52 am
declare @tbl table
(
ID int ,
NAME varchar(max),
STATE varchar(max),
[E.MAIL] varchar(max)
);
insert into @tbl
select 1,'JAMES',NULL, NULL union all
select 1, NULL ,'CA', NULL union all
select 1 ,NULL, NULL ,'abc@hotmail.com' union all
select 2,'steve',NULL, NULL union all
select 2, NULL ,'NA', NULL union all
select 2 ,NULL, NULL ,'xyz@hotmail.com'
select t1.id,t1.name,t2.state,t3.[e.mail] from @tbl t1
cross join @tbl t2
cross join @tbl t3
where t1.id=t2.id and t1.id=t3.id and t1.name is not null
and t2.state is not null and t3.[e.mail] is not null
January 20, 2011 at 3:53 am
declare @tbl table
(
ID int ,
NAME varchar(max),
STATE varchar(max),
[E.MAIL] varchar(max)
);
insert into @tbl
select 1,'JAMES',NULL, NULL union all
select 1, NULL ,'CA', NULL union all
select 1 ,NULL, NULL ,'abc@hotmail.com' union all
select 2,'steve',NULL, NULL union all
select 2, NULL ,'NA', NULL union all
select 2 ,NULL, NULL ,'xyz@hotmail.com'
select id,max(name),max(state),max([e.mail]) from @tbl
group by id
January 20, 2011 at 4:54 pm
create table #foo (I int,n varchar(200),o varchar(200),p varchar(200))
insert into #foo
SELECT 1, 'JAMES', NULL, NULL UNION
SELECT 2, NULL, 'CA', NULL UNION
SELECT 3, NULL, NULL, 'abc@hotmail.com' UNION
SELECT 4, 'CAMERON', NULL, NULL UNION
SELECT 5, NULL, 'IL', NULL UNION
SELECT 6, NULL, NULL, 'xyz@hotmail.com'
I want result like this
ID NAME STATE E.MAIL
1 JAMES CA abc@hotmail.com
Slight modification to the above query. If I have the Id values unique, how can I get the o/p that mentioned above?
January 20, 2011 at 6:07 pm
Have you tried any of the solutions provided?
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply