SQL Select stuff

Question

I have project table.

This is my query which is fetching following results.

select top 5 proj_ID, Proj_NM 
from project

Output:

proj_ID Proj_NM  
-------------------
 20     test1
 21     test2
 22     test3
 24     test4
 25     test5

I want to get this output instead. Can any one pls help.

proj_ID Proj_NM  All_Proj_NM
---------------------------------
 20     test1    test1,test2,test3,test4,test5
 21     test2    test1,test2,test3,test4,test5  
 22     test3    test1,test2,test3,test4,test5
 24     test4    test1,test2,test3,test4,test5
 25     test5    test1,test2,test3,test4,test5

Szymon · Accepted Answer · 2014-03-10T05:56:28.367

4

You can use FOR XML PATH for that

select top 5 proj_ID, Proj_NM,
    (select STUFF( (select top 5 ',' + Proj_NM 
                from project 
                order by proj_id
                FOR XML PATH('')
            ), 1, 1, '')) AS All_Proj_NM
from project
order by proj_ID

edited Mar 10 '14 at 05:56

answered Mar 10 '14 at 05:44

Szymon

42,577
16
96
114

This query will fetch all the Project names. But i want only the O/P project Names. – sk7730 Mar 10 '14 at 05:50
What do you mean by O/P project names? It outputs what you said above. – Szymon Mar 10 '14 at 05:51
For eg: If i apply filter condtions to get result set, only the o/p Project names should be a comma separated in in All_Proj_NM column. – sk7730 Mar 10 '14 at 05:53
i meant, in case of select top 5, only the top 5 Project names should be in All_Proj_NM column. – sk7730 Mar 10 '14 at 05:53
ok, I updated the answer. You just need to modify both outer and inner query. I added `order by` to make sure it returns the same 5 items in each case. – Szymon Mar 10 '14 at 05:57
Cool. My scenario is having almost 25 where condions to be applied. I think i have to define temp table with where condtions applied and should use it as a inner query in my actual select statment. – sk7730 Mar 10 '14 at 06:03

Vignesh Kumar A · Answer 2 · 2014-03-10T05:57:33.170

1

Try this

Select Distinct ST2.proj_ID,ST2.Proj_NM,
        substring((Select ',' + ST1.Proj_NM AS [text()]
        From project ST1  
        WHERE ST1.Proj_NM IN (SELECT Top 5 Proj_NM From Projects )                      
        ORDER BY ST1.proj_ID
        For XML PATH ('')),2, 1000) [Pr_Name]
 From dbo.project ST2

edited Mar 10 '14 at 05:57

answered Mar 10 '14 at 05:51

Vignesh Kumar A

27,863
13
63
115

In case of select top 5, only the top 5 Project names should be in All_Proj_NM column. This query will have all Project Names in [Pr_Name]. – sk7730 Mar 10 '14 at 05:55
@saravanakumarr Check my updation, you can change 5 with your custom number. or else you ca add condition in that where part – Vignesh Kumar A Mar 10 '14 at 05:58

SQL Select stuff

2 Answers2

Linked