Help me SQL Select

  • 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.

  • 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

  • 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’! **
  • 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