Query To Reorganize Data - SQL Server Forums

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

SQL Server 2008 Forums

Transact-SQL (2008)

Query To Reorganize Data

Author

Topic

ucal
Yak Posting Veteran

72 Posts

Posted - 2010-11-21 : 23:30:42

I have a table with the following Data:

ID FieldX
1 500FHHHF
2 JDJJJK
3 JKK KK
4 500PIII
5 LPPPPP

I will like to generate the folowing from the table such that the velues in FieldX starting with 500 is combined with subsequent records till a record starting with 500 is encounted.

Expected result is as follows.

FieldY
--------
500FHHHF JDJJJK JKK KK ---Record 1 to 3
500PIII LPPPPP --- Recod 4 and 5

eralper
Yak Posting Veteran

66 Posts

Posted - 2010-11-22 : 03:30:45

Hello Ucal,
Please try the following select statement

with cte as (
select id from Table153210 where fieldx like '500%'
),
cte2 as (
select
t.id,
t.fieldx,
(select max(id) from cte where id <= t.id) mid
from Table153210 t
)
SELECT
c.id,
STUFF(
(
SELECT ' ' + fieldx
from cte2
where cte2.mid = c.id
FOR XML PATH('')
), 1, 1, '') as f
FROM cte c

For more samples please check [url]http://www.kodyaz.com/articles/t-sql-concatenation-select-phone-numbers-as-home-office-other-by-sql-concatenate.aspx[/url] or [url]http://www.kodyaz.com/articles/concatenate-using-xml-path.aspx[/url] samples

-------------
Eralper
http://www.kodyaz.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources