SQLTeam.com | Weblogs | Forums

SQL Cursor Trouble

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?

1

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 !!!
:slight_smile:

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 :smiley:

2

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 NULL

hi 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 :heartbeat:

thanks Wim !!! are you new to SQL learning!!!! ???

:handshake::handshake:

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 ..

:+1::+1:

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

:+1::+1:

Nice!

If becoming an expert of experts
Is my goal

You're almost there!

:beer: :beer: :beer:

Thanks Wim

One of the areas of my interest is
How to become an Expert Very Very Fast ...

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. :neutral_face:
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.

Hi Wim

Along the same lines !!!
Please Please Please forgive if the below is OUT of line

Also GRAZ
... please let me know if i should not be posting any of this
I will take it offline !!!!

How people considered geniuses DID it !!! Everybody has to do the same things
But how they do it !!!
Again time factor ... doing it much much faster than any one else

These days machines ( artifical intelligence ) is being built
so that GENIUSES are not required !!!!
All that stuff can be done very very very fast and accuratley with software and hardware

This stuff is being built to solve many of mankind's biggest problems
today !!!
Example in ... pharmacy ... protein folding
Pollution ...
Hunger
Universal Basic Income for Every Living Human ( at least 1000 dollars a month
for everybody )

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.