Select 2 lines into 1
-
my file is very big and each 2 lines I need concat on one...ex: read first line and then read second line and save it on the first line...well I'm doing this, but it's not working...any idea?
delete from temp where col001 is null
DELETE FROM temp1
DECLARE @col001 CHAR(255)
DECLARE @COL002 CHAR(255)
DECLARE csrSites CURSOR FOR
SELECT col001 FROM temp
OPEN csrSites
FETCH NEXT FROM csrSites INTO @Col001
FETCH NEXT FROM csrSites INTO @Col002
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO temp1 (col001)
SELECT substring(col001,1,150) as FIRSTLINE FROM temp
FETCH NEXT FROM csrSites INTO @col001
INSERT INTO temp1 (col002)
SELECT substring(col001,1,100) as SECONDLINE FROM temp
FETCH NEXT FROM CSRsITES INTO @COL002
END
SELECT *
FROM TEMP1
CLOSE csrSites
DEALLOCATE csrSites
-
I must be missing something. You are assigning values to @col001 and @col002, but never using them. Also - what happens if there are an odd number of rows in temp?
Anyway - does this help?
Declare csrSites -- the way you defined the cursor
Open csrSites
FETCH NEXT FROM csrSires INTO @Col001
WHILE @@FETCH_STATUS = =
BEGIN
FETCH NEXT FROM csrSites INTO @col002
INSERT INTO temp1 VALUES
( SUBSTRING( @col001, 1, 150 ),
SUBSTRING( @col002, 1, 100 ) )
IF @@FETCH_STATUS = 0
FETCH NEXT FROM csrSites INTO @col001
ELSE
SET @col001 = NULL
END
IF @col001 IS NOT NULL
INSERT INTO temp1 VALUES
( SUBSTRING( @col001, 1, 150 ), NULL )
CLOSE csrSites
DEALLOCATE csrSites
select * from temp1
Guarddata-
-
If you table has an Identity column on it with an increment of 1 you can use a select statement to do this which uses the modulo operator to separate odd and even numbered rows, e.g.
SELECT Evens.MyIdCol AS EvenIdCol,
Odds.MyIdCol AS OddIdCol
FROM
(SELECT [MyIdCol], ([MyIdCol]/2) AS Base
FROM [MyTbl]
WHERE [MyIdCol] % 2 = 0) Evens
FULL OUTER JOIN
(SELECT [MyIdCol], ([MyIdCol]/2) AS Base
FROM [MyTbl]
WHERE [MyIdCol] % 2 = 1) Odds
ON Evens.Base = Odds.Base
Just one quick note - if there are missing numbers you will get some gaps!
-
Just to let any repliers to this topic know...Nelson posted this question twice. There are two GENERAL forums and this is in both. The other forum has the solution.
For those who find this one:
Nelson is doing TWO INSERTS that's why the data is not being concatenated into one row.
Every INSERT creates a new row. What he really needed to do is one of these:
1. Do one INSERT with the data concatenated in the single INSERT.
2. Do an INSERT for the first part of the data and then an UPDATE to add the second part of the data.
-SQLBill
-
SQLBill, I am sure you didn't mean for your message to sound the way I read it. There are a number of ways to address most issues so there is seldom a single solution that can be considered "the one". If you review the two suggestions here, you will find them viable.
Actually, the solution of inserting with two values is generally better than inserting a portion of data, then updating with more.
The solution I proposed is similar to the one you had proposed on the other forum. You concatenated the two entries, I put the two into separate columns. Not much difference.
Not a big deal - hope things are going better for you this afternoon.
Guarddata-
-
guarddata,
You are right, at times there are several viable solutions.
What I was intending to do was let everyone know this was a double post and the other post had been getting responses and a solution before anyone ever responded to this post.
Too many times I've seen double posts and comments/suggestions are going on in both when they would be better off all being in one.
I apologize for the way I came across.
-SQLBill
-
No problem at all. I have great respect for your abilities and have learned much from your posts. I also wish people would just post to a single forum - too much of "google" mentality or whatever the history might have been. Keep posting, my friend.
Guarddata-
-
I did post, in the Suggestion forum, the question about there being TWO General forums. Turns out they meant for one to be General SQL Server questions and the other to be General Programming questions. They are going to look into fixing it. I suggested either combining them or just doing:
General - SQL Server
General - Programming
Someone else suggested:
General
Major
-SQLBill
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply