In this article I am going to show how we can show 1-
Many and Many -1 type of relationship in SQL Server.
For example: I have to fetch multiple records
corresponding to one record in a single row.
How I can achieve this business problem. Let's do it by
an example.
Suppose I have a table
User_Registration
Where I have user registration related information like
below:
CREATE TABLE [dbo].[User_Registration](
[User_ID]
[int] IDENTITY(1,1) NOT NULL,
[Name]
[varchar](50) NULL,
[City]
[varchar](50) NULL,
[Country]
[varchar](50) NULL,
[Mobile]
[varchar](50) NULL,
CONSTRAINT [PK_User] PRIMARY
KEY CLUSTERED
(
[User_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS
= ON) ON [PRIMARY]
) ON [PRIMARY]
Image 1.
And I have some records in this table:
Image 2.
Now I have one more Table ie:
User_Friend
Where
I have Friend information.
CREATE TABLE [dbo].[User_Friend](
[User_ID]
[int] NULL,
[Friend_ID]
[int] NULL,
[Friend_Date]
[datetime] NULL
) ON [PRIMARY]
Image 3.
From this table I can know which user's Friend related
information
Image 4.
Now as per business need I need to fetch all friends'
information in a single row. Like below:
Image 5.
So below is the Query to achieve this J
SELECT USER_ID,
Friend_ID=STUFF
(
(
SELECT DISTINCT ', '+ CAST(g.Name AS VARCHAR(MAX))
FROM User_Registration g,
User_Friend e
WHERE g.User_ID=e.Friend_ID and e.User_ID=t1.User_ID
FOR XMl PATH('')
),1,1,''
)
FROM User_Friend t1
GROUP BY User_ID
Image 6.