July 17, 2013 at 9:32 pm
I have sample data as below:
IDActivitySubject ProjectName Fullname CusName
2 Tuan NULL NULL Aquafina
2 Tuan NULL T1 NULL
2 Tuan Test NULL NULL
2 Tuan NULL NULL NULL
3 ABC NULL NULL NULL
3 ABC Test NULL NULL
And I want Select Result as below:
IDActivitySubject ProjectName Fullname CusName
2 Tuan Test Test T1 Aquafina
3 ABC Test NULL NULL
Every one, please.
Thank you very much.
July 18, 2013 at 12:14 am
vantuan02t1 (7/17/2013)
I have sample data as below:IDActivitySubject ProjectName Fullname CusName
2 Tuan NULL NULL Aquafina
2 Tuan NULL T1 NULL
2 Tuan Test NULL NULL
2 Tuan NULL NULL NULL
3 ABC NULL NULL NULL
3 ABC Test NULL NULL
And I want Select Result as below:
IDActivitySubject ProjectName Fullname CusName
2 Tuan Test Test T1 Aquafina
3 ABC Test NULL NULL
Every one, please.
Thank you very much.
check this solution :
Create table #t1 ( IDActivity int,Subject varchar(10), ProjectName varchar(10), Fullnamevarchar(10), CusName varchar(10))
insert into #t1
Select 2, 'Tuan', NULL, NULL, 'Aquafina' union all
Select 2, 'Tuan' ,NULL, 'T1' ,NULL union all
Select 2, 'Tuan' ,'Test', NULL, NULL union all
Select 2, 'Tuan', NULL, NULL ,NULL union all
Select 3, 'ABC', NULL, NULL, NULL union all
Select 3, 'ABC', 'Test', NULL, NULL
select distinct IDActivity , (
Select distinct Subject From #t1 t2 where T2.Subject is not null And T2.IDActivity = T1.IDActivity
) Subject , (
Select distinct ProjectName From #t1 t3 where T3.ProjectName is not null And T3.IDActivity = T1.IDActivity
) ProjectName , (
Select distinct Fullname From #t1 t4 where T4.Fullname is not null And T4.IDActivity = T1.IDActivity
) Fullname , (
Select distinct CusName From #t1 t5 where T5.CusName is not null And T5.IDActivity = T1.IDActivity
) CusName
From #t1 t1
Neeraj Prasad Sharma
Sql Server Tutorials
July 18, 2013 at 12:34 am
If you want the highest value of multiple rows, this is also a working solution:
select
IDActivity
, MAX(Subject) as 'Subject'
, MAX(ProjectName) as 'ProjectName'
, MAX(Fullname) as 'Fullname'
, MAX(CusName) as 'CusName'
from table_name
group by IDActivity
July 18, 2013 at 7:45 pm
Thank you very much, I done finished
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply