Joining Row1 with Row2, Row3 with Row4

I have a little issue. I need to be able to concatonate the Row1 with Row2 to another/row/table, and the start again on the 3rd row, Join Row3 on Row4 .. and so forth.

The issue i have is that there is no relationship between Row1 and Row2. The only common thing is that Row2 is part of Row1.

Can this be done? (Can be done with either by SQL SERVER, SSIS or ASP.NET VB)

Below is a sample output of the data in column1 (single column)

ROW1: 000201031993 JOHN SMITH         Enabled             02/10/2011 08:43:13 
ROW2: -->Key Fob/Passcode/AES              02/14/2008 00:00:00   07/31/2011 00:00

ROW3: 000201031994 FOO GLOO          Enabled             02/13/2011 11:01:58 
ROW4: -->Key Fob/Passcode/AES              02/14/2008 00:00:00   07/31/2011 00:00

There is also other rubbish (header info etc开发者_如何学编程) at the start of the table (first few columns) (which was imported via SSIS from a csv) but this can be cleaned up manually (not preffered) each time before the import ..

Update:

I am trying to add the ROWID into the table during the csv converision via my Cursor in a StoredProcedure. However, i am having difficulty. The issue i am having is that (i think) the ROWID variable i have set keeps getting deallocated, hence not able to keep a count.

Below is my SP (without the ROWID lines). Any idea how i can do this. I would do an insert afterwards but that just adds the rows at the bottom of the table.

Declare @success integer
Declare @Var_RowID integer
Declare @Var_TokenID integer
Declare @Var_DisplayName varchar(50)
Declare @Var_TokenStatus varchar(30)
Declare @Var_ImportDate datetime

DECLARE c1 cursor FAST_FORWARD for 

SELECT 

LTRIM(RTRIM(LEFT(rawdata,12))) as TokenID_V,
LTRIM(RTRIM(SUBSTRING(rawdata, 13, (35 - 13)))) as DisplayName_V,
LTRIM(RTRIM(Replace((Substring(substring(rawdata, 35, LEN(rawdata)),1,20)),'.',''))) as TokenStatus_v,
Getdate() as ImportDate_V
FROM TokenDataInput
WHERE RawData like ('0%')
ORDER BY TokenID_V ASC

OPEN c1
FETCH NEXT FROM c1 INTO

@Var_TokenID,
@Var_DisplayName,
@Var_TokenStatus,
@Var_ImportDate

WHILE @@FETCH_STATUS = 0
BEGIN

INSERT INTO dbo.TokenData_Formatted(DisplayName, TokenStatus, ImportData)
VALUES(@Var_TokenID, @Var_DisplayName, @Var_TokenStatus, @Var_ImportDate)

FETCH NEXT FROM c1 INTO

@Var_TokenID,
@Var_DisplayName,
@Var_TokenStatus,
@Var_ImportDate

END
CLOSE C1
DEALLOCATE C1

SET @success = 1

RETURN @success

END
enter code here

Also, I tried to use Cursor.rowID but got the "Multipart indetifier" .. count not be bound.

Does the cursor have a built in rowcount which I could tap onto?

If you had a int id column that numbered the rows... sequentially you could do something like this...

SELECT TOP 1000 
      t1.[name],
      t1.[row_id],
       t2.[name],
      t2.[row_id]  
  FROM [testTable] t1
  inner join [testTable] t2 on t1.row_id = (t2.row_id +1)
  where (t1.row_id % 2) = 1

If you don't have such a column you could use...

alter table testTable add row_id int identity(1,1)

so your query might look like this...

SELECT t1.[Data] + ' ' + t2.[Data]
    FROM [testTable] t1
        inner join [testTable] t2 
            on t1.row_id = (t2.row_id +1)
    where (t1.row_id % 2) = 1

The first thing you need to understand is that without an ORDER BY clause, there is no 100% guarantee on the output order of your table rows. What you have imported from CSV could be in-order, but you must have a key within the CSV data, or an IDENTITY column that preserves insert order for this to work.

Once you get past that, jsobo's query works great by splitting the data on the odd columns and joining to the even.

I may not be follwing exactly what you are doing here, so I apologize if I'm off track but could a little DOS BAT file not do the concat for you and then you can pull it in where you need it?

@echo off
Setlocal EnableDelayedExpansion
set oddRow=Empty

FOR /F "tokens=* delims= " %%A in (c:\temp\test.txt) do (
   if "!oddRow!" == "Empty" (
      set oddRow=%%A
   ) else (
      echo !oddRow! %%A >> testout.txt
      set  oddRow=Empty
   )
)