April 10, 2008 at 6:19 am
Dear All,
I Have 2 tables Table1 and Table2
The Table1 is structure is like
Pno bigint(Primary Key)
Code varChar
Name varChar
The Table2 is structure is like
FormId bigint(primary key)
Pno bigint (Forgien Key)
Item varChar
Data in Table1 will be of
Pno Code Name
1 C-1 AAa
2 C-2 BBb
Data in Table2 will be
FormId Pno Item
1 1 Item1
2 1 Item2
3 2 Item3
4 2 Item4
I want the Output likse
Pno Code Name Items
1 C-1 AAa Item1,Item2
2 C-2 BBb Item3,Item4
April 10, 2008 at 6:23 am
Prakash:
For this particular problem it is critical to know which version of SQL Server you are using. If you are using SQL Server 2005 the "easiest" solution is to use XML capabilities to string the data together. Here is a page from the MSDN Common Solutions that addresses this particular solution:
April 11, 2008 at 4:19 am
Solution for these is
CREATE FUNCTION dbo.fnMakeTeamList
( @TeamID int )
RETURNS varchar(1000)
AS
BEGIN
DECLARE @TempTeam table
( Firstname varchar(20) )
DECLARE @TeamList varchar(1000)
SET @TeamList = ''
INSERT INTO @TempTeam
SELECT FirstName
FROM TeamInfo
WHERE TeamID = @TeamID
IF @@ROWCOUNT > 0
UPDATE @TempTeam
SET @TeamList = ( @TeamList + FirstName + ', ' )
RETURN substring( @TeamList, 1, ( len( @TeamList ) - 1 ))
END
-- Usage
SELECT
TeamID,
MemberList = dbo.fnMakeTeamList( TeamId )
FROM TeamInfo
GROUP BY TeamID
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply