Splitting Rows in SQL Server 2005

rajanandal

hi, Please help me.. i ve a table like this, Sentby SentOn AcceptBy AcceptOn 1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.753 32 2009-06-29 13:36:59.450 1 2009-06-30 12:25:45.303 1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.000 1 2009-06-16 13:19:34.693 1 2009-06-16 13:19:34.693 1 2009-06-15 19:40:36.000 1 2009-06-15 19:40:36.000 2 2009-06-15 19:40:36.000 1 2009-06-16 19:40:36.000 and i need like this Sentby SentOn 1 2009-06-15 19:40:36.000 32 2009-06-29 13:36:59.450 1 2009-06-30 12:25:45.303 1 2009-06-15 19:40:36.000 1 2009-06-16 13:19:34.693 1 2009-06-15 19:40:36.000 2 2009-06-15 19:40:36.000 1 2009-06-16 19:40:36.000 here in 2nd & 6th row ,Sentby and Accept by Columns are different so i need that in next row.

Nothing is Impossible. Keep always Smiling... :)

Niladri_Biswas

Try this(Assuming that the original table name is tblSENTBYSENTON)

ALTER PROCEDURE dbo.GETRECORDS
-- Add the parameters for the stored procedure here

AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- VARIABLE DECLARATION
DECLARE @SENTBY INT
DECLARE @SENTON DATETIME
DECLARE @ACCEPTBY INT
DECLARE @ACCEPTON DATETIME

DECLARE @NEWTBLSENTBYSENTON TABLE 
(
	SENTBY INT,
	SENTON DATETIME
)

-- STEP 1: DECLARE A CURSOR
DECLARE MYCURSOR CURSOR FOR

	SELECT    SENTBY,SENTON,ACCEPTBY,ACCEPTON
	FROM      TBLSENTBYSENTON

-- STEP 2: OPEN THE CURSOR
OPEN MYCURSOR 

FETCH MYCURSOR INTO @SENTBY,@SENTON,@ACCEPTBY,@ACCEPTON

-- STEP 3: START THE LOGIC

WHILE @@Fetch\_Status = 0

BEGIN

	-- STEP 4: INSERT RECORDS INTO TABLE @NEWTBLSENTBYSENTON
	--		   BASED ON THE LOGIC PROVIDED

		IF(@SENTBY = @ACCEPTBY)

		BEGIN
			INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
			VALUES(@SENTBY,@SENTON)
		END

		ELSE

		BEGIN

			INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
			VALUES(@SENTBY,@SENTON)

			INSERT INTO @NEWTBLSENTBYSENTON(SENTBY,SENTON)
			VALUES(@ACCEPTBY,@ACCEPTON)
		END


-- STEP 5: GET THE NEXT RECORD
FETCH MYCURSOR INTO @SENTBY,@SENTON,@ACCEPTBY,@ACCEPTON

END

--STEP 6: CLOSE THE CURSOR
CLOSE MYCURSOR

--STEP 7: DEALLOCATE THE CURSOR
DEALLOCATE MYCURSOR

SELECT \* FROM @NEWTBLSENTBYSENTON

END

With the same input, I got the same output:

Sentby SentOn
1 2009-06-15 19:40:36.000
32 2009-06-29 13:36:59.450
1 2009-06-30 12:25:45.303
1 2009-06-15 19:40:36.000
1 2009-06-16 13:19:34.693
1 2009-06-15 19:40:36.000
2 2009-06-15 19:40:36.000
1 2009-06-16 19:40:36.000

Hope this helps. :)

Niladri Biswas

modified on Wednesday, July 1, 2009 2:11 AM

rajanandal · modified on Wednesday, July 1, 2009 2:11 AM

Thanks for reply.... but i need a same result in select query without using Cursor,temptable,looping etc., because superior says it is a time consuming process.

Nothing is Impossible. Keep always Smiling... :)

Niladri_Biswas

Try this

SELECT Sentby,SentOn

	FROM tblSentBySentOn

	UNION 

	SELECT AcceptBy,AcceptOn

	FROM tblSentBySentOn

	WHERE Sentby <> AcceptBy

For grouping the results use this

SELECT A.Sentby, A.SentOn

FROM(
SELECT Sentby,SentOn

	FROM tblSentBySentOn

	UNION 

	SELECT AcceptBy,AcceptOn

	FROM tblSentBySentOn

	WHERE Sentby <> AcceptBy
) A ORDER BY A.SentOn

Hope this helps :)

Niladri Biswas

modified on Wednesday, July 1, 2009 3:56 AM

rajanandal · modified on Wednesday, July 1, 2009 3:56 AM

Thank you very much....Thanks for your timely help... :)

Nothing is Impossible. Keep always Smiling... :)

modified on Wednesday, July 1, 2009 3:30 AM