Pls. Help me in getting my desired output
-
Dear all,
I am stuck in obtaining my desired output. I have two tables A,B.
Both have RefNo as Comman Field.
I want my output in this format,
RefNo Pro_No Country
1. 11,22,33,44 X
2. 33,44,22,11 Y
3. 44,55,33,22 Z
Master Table A contains
RefNo Country
1. X
2. Y
3. Z
Detail TAble B contains
REfNo ProNo
1. 11
1. 22
1. 33
2. 44
2. 11
3. 33
Pls. Help me in getting this through Query.
Bye and Thanx in Advance
Kindest Regards,Neetu Sharma
-
Try the following code. ( Not tested, Check the syntax and correct it if it has problem). If RefNo is string in your tables, change the function accordingly.
CREATE FUNCTION dbo.udf_GetProNo(@RefNo int)
RETURNS nvarchar(1000)
AS
BEGIN
DECLARE @Pro_No nvarchar(1000)
SET @Pro_No=N''
SELECT @Pro_No=@Pro_No+CAST(ProNo as nvarchar(25))+',' FROM TableB WHERE RefNo=@RefNo
IF @Pro_No<>N''
BEGIN
SET @Pro_No=LEFT(@Pro_No,LEN(@Pro_No)-1)
END
RETURN @Pro_No
END
GO
SELECT RefNo,dbo.udf_GetProNo(ProNo) AS Pro_No,Country
FROM TableA
-
I don't get it, Neetu... you posted...
RefNo Pro_No Country
1. 11,22,33,44 X
2. 33,44,22,11 Y
3. 44,55,33,22 Z
Explain to me why 44 is in RefNo 1, why 33,44 is in RefNo2, and why 22,44,55 is in RefNo 3? It's not in the data you provided...
- REfNo ProNo
- 1. 11
- 1. 22
- 1. 33
- 2. 44
- 2. 11
- 3. 33
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Hello,
actually i wanted to say that in our Table B.
the data is present in this way:-
Ref_No Pro_No
1 11
1 22
1 33
1 44
2 33
2 44
2 22
2 11
3 44
3 55
3 33
3 22
Now data is in correct format. now plz tell us how i'll extract data from Table A and Table B in our desired format..........thanx
Kindest Regards,Neetu Sharma
-
Dear Neetu,
You need to make very small change in your table B, Add one primary key ProId as follow
CREATE TABLE B (
ProID int IDENTITY (1, 1) NOT NULL ,
RefNo int NOT NULL ,
ProNo int NULL ,
CONSTRAINT PK_B PRIMARY KEY CLUSTERED
(
ProID
 
ON PRIMARY) ON PRIMARY
GO
-----------------------------------------------
Now, follows my code
CREATE Function udf_ProNo
(
@RefNo int
)
Returns Varchar(100)
AS
BEGIN
DECLARE --@RefNo varchar(100),
@Count int,
@id int,
@id1 int,
@Store varchar(100),
@String varchar(5000)
SET @Store = ''
SET @String = ''
--SET @RefNo = 'manmohan'
SELECT @Count = max(ProID) FROM B WHERE RefNo like @RefNo
--SELECT @Count
SELECT @id = min([ProID]) FROM B WHERE RefNo like @RefNo
WHILE @Count <> 0
BEGIN
SELECT @Store = ProNo FROM B WHERE RefNo like @RefNo and [ProID]= @Count
IF Len(@Store) > 0
BEGIN
SET @String = @Store + ',' + @String
END
SET @Count = @Count - 1
SET @STORE = ''
END
RETURN (@String)
END
GO
---------------------------------------------------
Finally,
SELECT RefNo,dbo.udf_ProNo(RefNo),Country FROM A,
You will get your desired result.
Regds,
Vivek
/**A strong positive mental attitude will create more miracles than any wonder drug**/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply