Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Complex query

Author  Topic 

sveesam
Starting Member

2 Posts

Posted - 2014-09-03 : 02:58:48
I've a table with data like below:

RowNumber Pic Description
1 A A
2 NULL B
3 B A
4 C A
5 D A
6 NULL B
7 NULL C

And my output should be like below:

RowNumber Pic Description
1 A A - B
3 B A
4 C A
5 D A - B - C

Please help me with the query

Thanks in advance
Suresh

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-09-04 : 17:13:57
Maybe:
with cte1
as (select *
,RowNumber-1 as RowNumberPrev
from yourtable
where Pic is null
union all
select a.RowNumber
,b.Pic
,a.Description
,a.RowNumberPrev-1 as RowNumberPrev
from cte1 as a
inner join yourtable as b
on b.RowNumber=a.RowNumberPrev
where a.Pic is null
)
,cte2
as (select *
from yourtable
where Pic is not null
union all
select RowNumber
,Pic
,Description
from cte1
where Pic is not null
)
select a.Pic
,stuff((select ' - '+b.Description
from cte2 as b
where b.Pic=a.Pic
order by b.RowNumber
for xml path('')
),1,3,''
) as Description
from cte2 as a
group by a.Pic
order by a.Pic
Go to Top of Page

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2014-09-05 : 00:50:09
[code]
;with cte as
(
select t.RowNumber, Pic = coalesce(t.Pic, n.Pic), t.Description
from yourtable t
outer apply
(
select top 1 Pic
from yourtable x
where x.Pic is not null
and x.RowNumber < t.RowNumber
order by x.RowNumber desc
) n
)
select RowNumber = min(RowNumber), Pic,
Description = stuff((select ' - ' + Description from cte x where x.Pic = c.Pic for xml path('')), 1, 3, '')
from cte c
group by Pic[/code]


KH
[spoiler]Time is always against us[/spoiler]

Go to Top of Page
   

- Advertisement -