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
)
)
精彩评论