concatenate specific values from a column

Question

concatenate specific values from a column

Geoff A

SSChampion

Points: 11511
More actions
May 20, 2014 at 7:02 am

#285670

Give this data set;
declare @bo-2 table (Col1 varchar(50), Col2 varchar(50))
insert into @bo-2 values
('01','009920140516102116'),
('071710811019600001000005',''),
('070534524264000001000005',''),
('001806505517000001000005',''),
('001806595105200001000005',''),
('001806505903100001000005',''),
('02','009920140516102145'),
('000000002703000001000003',''),
('000000000259900001000003',''),
('000000007025800001000003',''),
('000000007056400001000003',''),
('000000001920500001000003',''),
('05','009920140516102250'),
('007648400910500001000005',''),
('007648400922800001000005',''),
('007648401441300001000005',''),
('007648401446800001000005',''),
('007648401460400001000005','')
select * from @bo-2
The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered.
The desired result set would look like;
declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values
('01','009920140516102116',''),
('071710811019600001000005','','01071710811019600001000005'),
('070534524264000001000005','','01070534524264000001000005'),
('001806505517000001000005','','01001806505517000001000005'),
('001806595105200001000005','','01001806595105200001000005'),
('001806505903100001000005','','01001806505903100001000005'),
('02','009920140516102145',''),
('000000002703000001000003','','02000000002703000001000003'),
('000000000259900001000003','','02000000000259900001000003'),
('000000007025800001000003','','02000000007025800001000003'),
('000000007056400001000003','','02000000007056400001000003'),
('000000001920500001000003','','02000000001920500001000003'),
('05','009920140516102250',''),
('007648400910500001000005','','05007648400910500001000005'),
('007648400922800001000005','','05007648400922800001000005'),
('007648401441300001000005','','05007648401441300001000005'),
('007648401446800001000005','','05007648401446800001000005'),
('007648401460400001000005','','05007648401460400001000005')
select * from @BOD

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

There is no way to do that with the sample data you provided. There is nothing to indicate an order and without a column to use as a sort there is no way to ensure the order of a query.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Geoff A SSChampion Points: 11511 More actions · Answer 2

I was came to that conclusion a few minutes ago....

my data will include an Identity column....

create table TEMPDB..BO ([ID] int, Col1 varchar(50), Col2 varchar(50))

insert into TEMPDB..BO values

(1,'01','009920140516102116'),

(2,'071710811019600001000005',''),

(3,'070534524264000001000005',''),

(4,'001806505517000001000005',''),

(5,'001806595105200001000005',''),

(6,'001806505903100001000005',''),

(7,'02','009920140516102145'),

(8,'000000002703000001000003',''),

(9,'000000000259900001000003',''),

(10,'000000007025800001000003',''),

(11,'000000007056400001000003',''),

(12,'000000001920500001000003',''),

(13,'05','009920140516102250'),

(14,'007648400910500001000005',''),

(15,'007648400922800001000005',''),

(16,'007648401441300001000005',''),

(17,'007648401446800001000005',''),

(18,'007648401460400001000005','')

select * from tempdb..BO

Sean Lange SSC Guru Points: 286590 More actions · Answer 3

Or maybe the actual order doesn't really matter and you can just do this?

select Col1,

Case LEN(Col1) when 2 then Col2 else '' end as Col2,

Case LEN(Col1) when 2 then '' else Col1 end as Col3

from @bo-2

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Geoff A SSChampion Points: 11511 More actions · Answer 4

i came up with a solution. not pretty but it will work for now...

declare @bo-2 table ([ID] int, Col1 varchar(50), Col2 varchar(50), col3 varchar(50))

insert into @bo-2 values

(1,'01','009920140516102116',''),

(2,'071710811019600001000007','',''),

(3,'070534524264000001000008','',''),

(4,'001806505517000001000009','',''),

(5,'001806595105200001000002','',''),

(6,'001806505903100001000003','',''),

(7,'02','009920140516102145',''),

(8,'000000002703000001000001','',''),

(9,'000000000259900001000002','',''),

(10,'000000007025800001000003','',''),

(11,'000000007056400001000004','',''),

(12,'000000001920500001000005','',''),

(13,'05','009920140516102250',''),

(14,'007648400910500001000001','',''),

(15,'007648400922800001000002','',''),

(16,'007648401441300001000003','',''),

(17,'007648401446800001000004','',''),

(18,'007648401460400001000005','','')

select * from @bo-2

create table tempdb..BO ([ID] int, col1 varchar(50), col2 varchar(50), col3 varchar(50))

insert into tempdb..BO

select * from @bo-2

declare @col1 varchar(50)

declare db_crsr_DBS cursor for

select [ID] from @bo-2 where col1 like '__'

open db_crsr_DBS

fetch next from db_crsr_DBS into @col1

while @@fetch_status = 0

begin

select top 2 [ID]

into #temp

from @bo-2 where col1 like '__'

declare @nextno int, @myval char(2)

set @nextno = (select max([ID]) from #temp)

set @myval = (select col1 from @bo-2 where [ID] = @col1)

if @col1 = @nextno

set @nextno = (select max([ID]) from @bo-2)

update T1

set T1.col3 = (@myval+''+ t2.col1)

from tempdb..BO t1

inner join tempdb..BO t2

on t1.col1 = t2.col1 and t1.[ID] <= @nextno and t1.[ID] > @col1 and t1.col2 = ''

delete from @bo-2 where ID < @nextno

fetch next from db_crsr_DBS into @col1

drop table #temp

end

close db_crsr_DBS

deallocate db_crsr_DBS

select * from tempdb..BO

drop table tempdb..BO

Luis Cazares SSC Guru Points: 183706 More actions · Answer 5

It's still not pretty, it will read the table 4 times, but it should work better than the cursor. The recommendation is that you test both solutions to check which one will perform better.

WITH shortvalues AS(

SELECT ID, Col1, ROW_NUMBER() OVER( ORDER BY [ID]) rn

FROM @bo-2

WHERE LEN( Col1) = 2

)

,Ranges AS(

SELECT a.Col1,

a.ID AS Low,

ISNULL( b.ID, (SELECT MAX(ID) + 1 FROM @bo-2)) AS High

FROM shortvalues a

LEFT

JOIN shortvalues b ON a.rn = b.rn - 1

)

SELECT b.ID,

r.Col1 + CASE WHEN b.ID = r.Low

THEN b.Col2

ELSE b.Col1 END

FROM @bo-2 b

JOIN Ranges r ON b.ID >= r.Low AND b.ID < r.High

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Sean Lange SSC Guru Points: 286590 More actions · Answer 6

Geoff A (5/20/2014)
i came up with a solution. not pretty but it will work for now...
declare @bo-2 table ([ID] int, Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @bo-2 values
(1,'01','009920140516102116',''),
(2,'071710811019600001000007','',''),
(3,'070534524264000001000008','',''),
(4,'001806505517000001000009','',''),
(5,'001806595105200001000002','',''),
(6,'001806505903100001000003','',''),
(7,'02','009920140516102145',''),
(8,'000000002703000001000001','',''),
(9,'000000000259900001000002','',''),
(10,'000000007025800001000003','',''),
(11,'000000007056400001000004','',''),
(12,'000000001920500001000005','',''),
(13,'05','009920140516102250',''),
(14,'007648400910500001000001','',''),
(15,'007648400922800001000002','',''),
(16,'007648401441300001000003','',''),
(17,'007648401446800001000004','',''),
(18,'007648401460400001000005','','')
select * from @bo-2
create table tempdb..BO ([ID] int, col1 varchar(50), col2 varchar(50), col3 varchar(50))
insert into tempdb..BO
select * from @bo-2
declare @col1 varchar(50)
declare db_crsr_DBS cursor for
select [ID] from @bo-2 where col1 like '__'
open db_crsr_DBS
fetch next from db_crsr_DBS into @col1
while @@fetch_status = 0
begin
select top 2 [ID]
into #temp
from @bo-2 where col1 like '__'
declare @nextno int, @myval char(2)
set @nextno = (select max([ID]) from #temp)
set @myval = (select col1 from @bo-2 where [ID] = @col1)
if @col1 = @nextno
set @nextno = (select max([ID]) from @bo-2)
update T1
set T1.col3 = (@myval+''+ t2.col1)
from tempdb..BO t1
inner join tempdb..BO t2
on t1.col1 = t2.col1 and t1.[ID] <= @nextno and t1.[ID] > @col1 and t1.col2 = ''
delete from @bo-2 where ID < @nextno
fetch next from db_crsr_DBS into @col1
drop table #temp
end
close db_crsr_DBS
deallocate db_crsr_DBS
select * from tempdb..BO
drop table tempdb..BO

You definitely don't want or need a cursor for this. You should never say things like "it is good enough for now". What that really means is that you think it is good enough. You will never find the time to go back and do this right.

What is wrong with the incredibly simple code I posted? It produces the same output as you stated you wanted in your first post.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Geoff A SSChampion Points: 11511 More actions · Answer 7

Luis Cazares (5/20/2014)
It's still not pretty, it will read the table 4 times, but it should work better than the cursor. The recommendation is that you test both solutions to check which one will perform better.
WITH shortvalues AS(
SELECT ID, Col1, ROW_NUMBER() OVER( ORDER BY [ID]) rn
FROM @bo-2
WHERE LEN( Col1) = 2
)
,Ranges AS(
SELECT a.Col1,
a.ID AS Low,
ISNULL( b.ID, (SELECT MAX(ID) + 1 FROM @bo-2)) AS High
FROM shortvalues a
LEFT
JOIN shortvalues b ON a.rn = b.rn - 1
)
SELECT b.ID,
r.Col1 + CASE WHEN b.ID = r.Low
THEN b.Col2
ELSE b.Col1 END
FROM @bo-2 b
JOIN Ranges r ON b.ID >= r.Low AND b.ID < r.High

thanks! this works perfectly.