How to use joins and make Normalised data to denormalised data-CodePudding

I have a database with the following tables :

 UserTable :
       -----UserID (int) PK,
       -----UserName (Varchar) null

 PhoneNumber :
       ------PhoID (int) PK,
       ------UserID(int) FK to UserTable
       ------PhoneNumber (Varchar) null

 Address
       ------AddressID (int) PK,
       ------UserID(int) FK to UserTable
       ------AddressName (Varchar) null.

The users can have many phone numbers and many addresses. It is a one to many relationship.

I have the following data in these tables.

UserID UserName
1      Bridgerton
2      Merlin
3      Victoria

PhoID   UserID  PhoneNumber
1      1      phone1
2      1      phone2
3      1      phone3
4      2      Phone21
5      2      9909909900

AddressID   UserID  AddressName
1            1        Chennai
2            1        Gurgaon
3            2        Hyderabad
4            2        Mumbai
5            2        Gurgaon

Now I need the following result, want to see the userdetails, his phone number and the list of addressess in one go.

I wrote the following query,

select UserName, PhoneNumber, AddressName
from dbo.UserTable a 
left outer join dbo.PhoneNumber b 
  on a.UserID=b.UserID
left outer join dbo.Address c
 on a.UserID = c.UserID

Results

UserName    PhoneNumber    AddressName
Bridgerton  phone1         Chennai
Bridgerton  phone1         Gurgaon
Bridgerton  phone2         Chennai
Bridgerton  phone2         Gurgaon
Bridgerton  phone3         Chennai
Bridgerton  phone3         Gurgaon
Merlin      phone21        Hyderabad
Merlin      phone21        Mumbai
Merlin      phone21        Gurgaon
Merlin      9909909900      Hyderabad
Merlin      9909909900      Mumbai
Merlin      9909909900      Gurgaon
Victoria    NULL           NULL

I know its messed up but need to see the username, userphone numbers and useraddress for a particular userid like this in just one record.

userid     username          Phonenumber                Address
1          Bridgerton        phone1, phone2, phone3       Gurgaon, Hyderabad

CodePudding user response：

One option is to use UNION ALL to normalize your data then then perform a simple conditional aggregation/string_agg()

Example

;with cte as (
Select UserID
      ,Seq = UserID
      ,Col = 'UserName'
      ,Val = UserName
 From UserTable 
Union All
Select UserID
      ,Seq = PhoID
      ,Col = 'Phone'
      ,Val = PhoneNumber
 From PhoneNumber
Union All
Select UserID
      ,Seq = AddressID
      ,Col = 'Address'
      ,Val = [AddressName]
 From Address
)
Select UserID
      ,UserName    = max(case when col='UserName' then Val end) 
      ,PhoneNumber = string_agg( case when col='Phone'   then Val end,',') within group ( order by seq)
      ,Address     = string_agg( case when col='Address' then Val end,',') within group ( order by seq)
 From  cte 
 Group By UserID

Results

UserID  UserName     PhoneNumber            Address
1       Bridgerton   phone1,phone2,phone3   Chennai,Gurgaon
2       Merlin       Phone21,9909909900     Hyderabad,Mumbai,Gurgaon
3       Victoria     NULL                   NULL