Hello, i have a large set of data and i need to copy the Path ( folder) for every line, for example first path it's for one .mdb, the second path is for next 5 rows. How can i acomplish that?
i modified the original post, can you have a look?
i think i understood what you want !!!
its my bedtime .. will take a look tomorrow !!!
1 Like
hi
first i created .... sample data
please click arrow to the left for drop create sample data script
drop table #sampledata
go
create table #sampledata
(
folder varchar(100) null,
folder2 varchar(100) null
)
go
insert into #sampledata select
null,null
insert into #sampledata select
null,null
insert into #sampledata select
'C:\okfine\nope\',null
insert into #sampledata select
null,'02/07/2010 09:45 583680 inr.mdb'
insert into #sampledata select
'C:\abc\def\',null
insert into #sampledata select
null,'18/11/2019 09:30 4194303 master.mdf'
insert into #sampledata select
null,'18/11/2019 09:30 1245184 model.mdf'
insert into #sampledata select
null,'18/11/2019 09:30 5111808 msdbdata.mdf'
insert into #sampledata select
'C:\aaa\ddd\',null
insert into #sampledata select
null,'19/11/2019 16:31 1060864 dhcp.mdb'
go
select * from #sampledata
go
1 Like
i want to be sure you understand 
got it .. i will do it in a few minutes and post it
hi
i tried to do this !!!! hope this helps !!!
i will try to come up with trick to make SQL very short and avoid recursive CTE ...
mine is a very long way of SQL ... doing
plus i used recursive CTE which should be avoided ( not good at all for performance )
I am sure seniors here in this forum
have very very shortcuts ( tricks for this kind of situation)
please click arrow to the left for drop create data script
drop table #sampledata
go
create table #sampledata
(
folder varchar(100) null,
folder2 varchar(100) null
)
go
insert into #sampledata select
null,null
insert into #sampledata select
null,null
insert into #sampledata select
'C:\okfine\nope\',null
insert into #sampledata select
null,'02/07/2010 09:45 583680 inr.mdb'
insert into #sampledata select
'C:\abc\def\',null
insert into #sampledata select
null,'18/11/2019 09:30 4194303 master.mdf'
insert into #sampledata select
null,'18/11/2019 09:30 1245184 model.mdf'
insert into #sampledata select
null,'18/11/2019 09:30 5111808 msdbdata.mdf'
insert into #sampledata select
'C:\aaa\ddd\',null
insert into #sampledata select
null,'19/11/2019 16:31 1060864 dhcp.mdb'
go
select 'sample data',* from #sampledata
go
please click arrow to the left for SQL Script
;WITH rownumber_adding_cte
AS (SELECT Row_number()
OVER(
ORDER BY (SELECT NULL)) AS rn,
*
FROM #sampledata),
recursive_cte
AS (SELECT *,
1 AS grp
FROM rownumber_adding_cte
WHERE rn = 1
UNION ALL
SELECT a.*,
CASE
WHEN a.folder IS NOT NULL THEN b.grp + 1
ELSE b.grp
END
FROM rownumber_adding_cte a
JOIN recursive_cte b
ON a.rn = b.rn + 1),
grp_max
AS (SELECT Max(folder) AS maxfld,
grp
FROM recursive_cte
GROUP BY grp)
SELECT 'SQL Output',
b.maxfld AS folder,
a.folder2
FROM recursive_cte a
JOIN grp_max b
ON a.grp = b.grp
DROP TABLE #sampledata
GO
CREATE TABLE #sampledata
(
id int IDENTITY(1, 1) NOT NULL PRIMARY KEY, --<<--!!
folder varchar(100) NULL,
folder2 varchar(100) NULL
)
GO
INSERT INTO #sampledata VALUES
(NULL,NULL),
(NULL,NULL),
('C:\okfine\nope\',NULL),
(NULL,'02/07/2010 09:45 583680 inr.mdb'),
('C:\abc\def\',NULL),
(NULL,'18/11/2019 09:30 4194303 master.mdf'),
(NULL,'18/11/2019 09:30 1245184 model.mdf'),
(NULL,'18/11/2019 09:30 5111808 msdbdata.mdf'),
('C:\aaa\ddd\',NULL),
(NULL,'19/11/2019 16:31 1060864 dhcp.mdb')
GO
--SELECT * FROM #sampledata
GO
;WITH dirs AS (
SELECT id, folder
FROM #sampledata
WHERE folder IS NOT NULL
)
SELECT d.folder, sd.folder2
FROM #sampledata sd
OUTER APPLY (
SELECT TOP (1) d.folder
FROM dirs d
WHERE d.id < sd.id
ORDER BY d.id DESC
) AS d
WHERE sd.folder IS NULL AND sd.folder2 IS NOT NULLhi i tried to do this in a different way !!!!
I added identity column to the table to use as row numbers
please click arrow to the left for drop create sample data ..
drop table #sampledata
go
create table #sampledata
(
id int identity(1,1) not null,
folder varchar(100) null,
folder2 varchar(100) null
)
go
insert into #sampledata select
null,null
insert into #sampledata select
null,null
insert into #sampledata select
'C:\okfine\nope\',null
insert into #sampledata select
null,'02/07/2010 09:45 583680 inr.mdb'
insert into #sampledata select
'C:\abc\def\',null
insert into #sampledata select
null,'18/11/2019 09:30 4194303 master.mdf'
insert into #sampledata select
null,'18/11/2019 09:30 1245184 model.mdf'
insert into #sampledata select
null,'18/11/2019 09:30 5111808 msdbdata.mdf'
insert into #sampledata select
'C:\aaa\ddd\',null
insert into #sampledata select
null,'19/11/2019 16:31 1060864 dhcp.mdb'
go
select 'sample data',* from #sampledata
go
please click arrow to the left for SQL different WAY finally working
; WITH cte
AS (SELECT id,
folder,
isnull(Lead(id)
OVER(
ORDER BY id ),id+2) AS nextid
FROM #sampledata
WHERE folder IS NOT NULL),
cte_all
AS (SELECT b.id,
a.folder
FROM cte a
JOIN #sampledata b
ON b.id BETWEEN a.id AND a.nextid - 1)
SELECT 'SQL Output different way',
CASE
WHEN a.id IS NULL THEN b.id
ELSE a.id
END AS id,
a.folder,
b.folder2
FROM cte_all a
RIGHT JOIN #sampledata b
ON a.id = b.id
1 Like
harishgg1,
Elegant solution 
thanks Wim !!! are you new to SQL learning!!!! ???
No, I have a few years of experience. But I'm new on this forum.
Started with Informix, DB2 later on and SQL Server for the last decade.
Wow nice ..
How about you?
Hi
I have over 5 years experience as a
SQL server developer
But I never took t SQL seriously
It's only in the last year
That I have been regularly practising
If becoming an expert of experts
Is my goal
It does not take that long
You can say
Medium causal t SQL enthusiast
Nice!
If becoming an expert of experts
Is my goal
You're almost there!
This is how I sharpened my T-SQL skills:
When I switched job, I knew nothing about SQL Server. I joined a SQL Server forum and started to read the questions and answers. Shortly after that I started to post answers myself with my DB2 experience. The few times I made a mistake, the other members on the forum would swiftly correct me. 
I learned a lot that way. Both by solving the questions myself and by trying to understand what others came up.
I remember how puzzled I was at first with the T-SQL "GO" statement.
You're very active on this forum and your solutions are sound and solid. You improve your own solutions when you think of a better way... Quite impressive. You're definitely on the highway to become an expert. Many will already consider you as one.
I am currently taking the Learning How to Learn online course. It's one of the best rated online courses around.
They don't teach how to become an expert, but give it a try, you may find it interesting and helpful. I do.
Look at AI as an opportunity.
I subscribed to an online course of data science, only to find out I need the mathematics from high school. High school lies quite some years in the past for me.
So I subscribed to a math rehearsal course. It's fun to relearn things you forgot, and it goes really fast.
Don't feel threatened by those developments. Look at them as opportunities and go for it.
I met a (competent) data scientist who was surprised how fast I could write a few lines of SQL code that gave the result in a split second instead of minutes as he was used to. Don't underestimate the value of your skills.
Maybe we should take this discussion off public, we stretched this "SQL Cursor Trouble" topic far enough. Not convinced the rest of the world is interested in our small-talk.
The most important feature of a cursor is to retrieve data, one row at a time, from a end result set, not like the SQL instructions which operate on all the rows in the end result set at one time. Cursors are used when the person wishes to update archives in a singleton fashion or in a row by means of row manner, in a database table.
Cursors in SQL Server are infamous for being slow. Cursors are discussed as if they were monsters to be avoided at all costs in any ideal programming environment. The rationale is simple: they are the most effective technique to slow down an application. This is due to the fact that SQL Server, like any other effective relational database management system (RDBMS), is designed to perform set-based operations.