April 18, 2016 at 5:28 am
Hello experts,
I have a table which consists of data as below
|--|---------|
|ID| SAPDATA |
|--|---------|
|1 | ABC |
|2 | 100 |
|3 | DEF |
|4 | 200 |
|5 | GHI |
|6 | 150 |
|7 | JKL |
|8 | 432 |
|9 | MNO |
|10| 777 |
|------------|
I need a sql query to return the resultant output which is concatenation of rows [1 and 2] , [3 and 4] , [5 and 6] , [7 and 8] , [9 and 10] and so on.. output should be as shown below
|--|-------|
|ID|SAPDATA|
|--|-------|
|1 |ABC100 |
|2 |DEF200 |
|3 |GHI150 |
|4 |JKL432 |
|5 |MNO777 |
|--|-------|
Can anyone help me on this ? Thanks in advance 🙂
April 18, 2016 at 5:35 am
create table Temp(id int,SAPDATA varchar(6))
insert into temp
select 1 ID, 'ABC' SAPDATA
UNION ALL
select 2 , '100'
UNION ALL SELECT 3 , 'DEF'
UNION ALL SELECT 4 , '200'
UNION ALL SELECT 5 , 'GHI'
UNION ALL SELECT 6 , '150'
UNION ALL SELECT 7 , 'JKL'
UNION ALL SELECT 8 , '432'
UNION ALL SELECT 9 , 'MNO'
UNION ALL SELECT 10, '777'
--ALL SQL Versions
select b.id/2 as ID,a.SAPDATA+b.SAPDATA as SAPDATA
from temp A
inner join temp b on a.id=b.id-1 and b.ID%2=0
--SQL2012+
select ID/2 as ID,SAPDATA from (
select ID,LAG(a.SAPDATA,1,'') OVER (order by ID asc) +a.SAPDATA as SAPDATA from temp A
)window WHERE ID%2=0
April 18, 2016 at 5:58 am
Or this one, which only scans the table once, although it does introduce a sort operation, so you'd want to test to see which performs better. My guess is that this one will be the faster if you have an index on ID. In both cases, it may not work if you have gaps in your ID sequence, so you might consider making the code more robust to handle such situations.
WITH ABC123 AS (
SELECT
ID
,SAPDATA + LEAD(SAPDATA,1) OVER (ORDER BY ID) AS SAPDATAConcat
FROM Temp
)
SELECT
(ID+1)/2 AS ID
,SAPDATAConcat AS SAPDATA
FROM ABC123
WHERE ID%2 = 1
John
April 18, 2016 at 6:02 am
Thanks , worked like a charm. 🙂
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply