Email: Password:       Forgot Password 
    .netCodeSG
A Saarsha Group Online Community for dot net codes group like C#, Asp.NET, VB.NET, Sharepoint, JavaScript, JQuery, Ajax, SQL, WCF, WPF.
 
TECHNOLOGIES:
 

SQL Server- One to Many and Many to One Relationship

Article:
Viewed:  54 
Posted On:  30/03/2018 12:29:25 
How we can show multiple associated record to one record in a single row. 

 

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]

 

1.png

Image 1.

And I have some records in this table:

2.png

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]

 

3.png

Image 3.

From this table I can know which user's Friend related information

4.png

Image 4.

Now as per business need I need to fetch all friends' information in a single row. Like below:

5.png

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 

 

6.png

Image 6.

  Comment:
         HOME   |   Submit Article   |   Contact Us   |   About Us   |   Terms & Condition   |   Advertise With us