Multiple row in self join

Question

i have table in this format

Id  QId   ResourceId ModuleId SubProjId  Comments 
1   1   1   1   2   ffdg    1   1
2   2   1   1   2   dfgfdg  1   1
3   3   1   1   2   hgjhg   1   1
4   1   2   1   2   tryty   1   0
5   5   1   1   2   sdf 1   1
6   5   2   1   2   ghgfh   1   0
7   7   2   1   2   tytry   1   0
8   3   2   1   2   rytr    1   0

and i wan result in this way

qid  ResourceId Comments   ResourceId  Comments

1   1   ffdg    2   tryty
3   1   hgjhg   2   rytr

i tried

select distinct  A.qid,A.ResourceId,A.Comments,B.ResourceId,b.Comments
from dbo.#temp A inner join #temp B on A.QId=B.QId and A.[ModuleId]=B.[ModuleId] and a.[SubProjId]=b.[SubProjId]

but did not find any luck please help

I hope this thread helps you. http://stackoverflow.com/questions/4071811/how-to-transform-vertical-data-into-horizontal-data-with-sql — Pavan, Dec 14 '11 at 05:41
@Nakul :-basically A.qid,A.ResourceId and [ModuleId] are the common — Rashmi Kant Shrivastwa, Dec 14 '11 at 06:00

score 0 · Answer 1 · edited May 23 '17 at 11:48

0

You want to convert vertical data to horizontal. So you need to create a pivot table. You can find more details here How to transform vertical data into horizontal data with SQL?

edited May 23 '17 at 11:48

Community

1
1

answered Dec 14 '11 at 05:44

Pavan

4,209
1
22
25

Then why ResourceId and Comments columns exists twice in the result? – Pavan Dec 14 '11 at 06:06
Rashmi Kant..From the result that you have posted, it appears to me that you want to convert vertical data to horizontal and you can do it using Pivot table. Please go thur the pivot table reference link and see if can help you. – Pavan Dec 14 '11 at 06:22
from my query result is coming but its also giving some extra row which i , do not want – Rashmi Kant Shrivastwa Dec 14 '11 at 06:33

Multiple row in self join

1 Answers1