December 7, 2012 at 4:31 am
Hi Team,
i've a table with below info.
Table Name : MNFC
Col_1Col_2Col_3
----- ----- -----
SalesYes12-Jan
ProdYesNULL
MarkNO15-Jan
FincNULLNULL
using a cursor and checking below conditions
for Col1, if "col2" and "col3" data is available then assign to a string
Sales :Yes Expires : 12 Jan
if Col_3 data is NULL then
Prod : Yes
if Col2 and Col3 is NULL then
Finc :
----
final String =
Sales :Yes Expires : 12 Jan
Prod : Yes
Finc :
------------
declare @FinalString varchar(max)
DECLARE AUR CURSOR FOR SELECT Col1 FROM MNFC
OPEN AUR
FETCH NEXT FROM AUR INTO @ur
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @FinalString=(SELECT
CASE WHEN Col2 IS NULL AND Col3IS NULL THEN
+ RTRIM(@ur) +CHAR(10)
ELSE CASE WHEN Col3 IS NULL THEN
+ RTRIM(@ur) + RTRIM(Col2) +CHAR(10)
ELSE
+ RTRIM(@ur) +RTRIM(Col2) + ' (Expires :' + RTRIM(CONVERT(VARCHAR, Col3,107)) + ')' +CHAR(10)
END
END
)
FROM MNFC WHERE Col1=@ur
print @FinalString
FETCH NEXT FROM AUR INTO @ur
END
CLOSE AUR
DEALLOCATE AUR
December 7, 2012 at 5:04 am
Why do you need a cursor for this?
Here's your sample data: -
CREATE TABLE MNFC (Col_1 VARCHAR(5),Col_2 VARCHAR(3),Col_3 VARCHAR(6));
INSERT INTO MNFC
SELECT 'Sales','Yes','12-Jan'
UNION ALL SELECT 'Prod','Yes',NULL
UNION ALL SELECT 'Mark','NO','15-Jan'
UNION ALL SELECT 'Finc',NULL,NULL;
So based on that, we'd execute the following: -
DECLARE @FinalString VARCHAR(MAX) = '';
SELECT @FinalString = @FinalString+CHAR(13)+CHAR(10)+Col_1+' :'+ISNULL(Col_2,'')+ISNULL(' Expires : '+Col_3,'')
FROM MNFC
WHERE Col_2 = 'Yes' OR Col_2 IS NULL;
SET @FinalString = STUFF(@FinalString,1,2,'');
PRINT @FinalString;
Which results in: -
--------------------------------
Sales :Yes Expires : 12-Jan
Prod :Yes
Finc :
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply