Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Help me SQL Select Expand / Collapse
Author
Message
Posted Wednesday, July 17, 2013 9:32 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 18, 2013 7:44 PM
Points: 18, Visits: 34
I have sample data as below:
IDActivity Subject 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:
IDActivity Subject ProjectName Fullname CusName
2 Tuan Test Test T1 Aquafina
3 ABC Test NULL NULL

Every one, please.
Thank you very much.
Post #1474872
Posted Thursday, July 18, 2013 12:14 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, September 27, 2013 5:37 AM
Points: 306, Visits: 524
vantuan02t1 (7/17/2013)
I have sample data as below:
IDActivity Subject 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:
IDActivity Subject 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), Fullname varchar(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
Post #1474898
Posted Thursday, July 18, 2013 12:34 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 7:59 AM
Points: 2,078, Visits: 2,410
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



** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
Post #1474904
Posted Thursday, July 18, 2013 7:45 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 18, 2013 7:44 PM
Points: 18, Visits: 34
Thank you very much, I done finished
Post #1475247
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse