jsonsql-serverjoinwith-clause

Need json value without re-joining the table in SQL Server

I have written the code shown below and I got the expected output. but,need the same output without use the same tables in inline view (such as tables join for UserPhoneDetail_JSON). thanks in advance

Code:

BEGIN
DROP TABLE #USERMASTER;
DROP TABLE #USERPHONE;
CREATE TABLE #USERMASTER (ID INT, NAME VARCHAR(100));
CREATE TABLE #USERPHONE (ID INT, PHONENUMBER NUMERIC,PHONETYPE CHAR(1));
INSERT INTO #USERMASTER VALUES(1,'JOHN');
INSERT INTO #USERMASTER VALUES(2,'VICTOR');
INSERT INTO #USERPHONE VALUES(1,1356487965,'W');
INSERT INTO #USERPHONE VALUES(1,9841007493,'M');
INSERT INTO #USERPHONE VALUES(1,7255952105,'O');
INSERT INTO #USERPHONE VALUES(2,9874563212,'M');
WITH E AS (SELECT A.ID,A.NAME,B.PHONENUMBER,B.PHONETYPE,ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY A.ID) RN
FROM #USERMASTER A JOIN #USERPHONE B ON  A.ID=B.ID)
SELECT E.ID,
E.NAME,
E.PHONENUMBER,
E.PHONETYPE,
UserPhoneDetail_JSON = (
                   SELECT A.ID,B.PHONETYPE,PHONENUMBER,A.NAME FROM #USERMASTER A JOIN #USERPHONE B ON  A.ID=B.ID
                    FOR JSON PATH )
FROM E WHERE RN=1;
END

output:

1   JOHN    1356487965  W   [{"ID":1,"PHONETYPE":"W","PHONENUMBER":1356487965,"NAME":"JOHN"},{"ID":1,"PHONETYPE":"M","PHONENUMBER":9841007493,"NAME":"JOHN"},{"ID":1,"PHONETYPE":"O","PHONENUMBER":7255952105,"NAME":"JOHN"},{"ID":2,"PHONETYPE":"M","PHONENUMBER":9874563212,"NAME":"VICTOR"}]
2   VICTOR  9874563212  M   [{"ID":1,"PHONETYPE":"W","PHONENUMBER":1356487965,"NAME":"JOHN"},{"ID":1,"PHONETYPE":"M","PHONENUMBER":9841007493,"NAME":"JOHN"},{"ID":1,"PHONETYPE":"O","PHONENUMBER":7255952105,"NAME":"JOHN"},{"ID":2,"PHONETYPE":"M","PHONENUMBER":9874563212,"NAME":"VICTOR"}]

EXPECTED OUTPUT:

1   JOHN    1356487965  W   [{"ID":1,"PHONETYPE":"W","PHONENUMBER":1356487965,"NAME":"JOHN"},{"ID":1,"PHONETYPE":"M","PHONENUMBER":9841007493,"NAME":"JOHN"},{"ID":1,"PHONETYPE":"O","PHONENUMBER":7255952105,"NAME":"JOHN"}]

2   VICTOR  9874563212  M   [{"ID":2,"PHONETYPE":"M","PHONENUMBER":9874563212,"NAME":"VICTOR"}]
Solution

  • If I understand you correctly and you need to generate JSON content for each first row per ID, this statement is an option:

    SELECT 
   t.ID, t.NAME, t.PHONENUMBER, t.PHONETYPE,
   UserPhoneDetail_JSON = (
     SELECT t.ID, PHONETYPE, PHONENUMBER, t.NAME 
     FROM #USERPHONE 
     WHERE ID = t.ID
     FOR JSON PATH 
  )
FROM (
   SELECT 
      m.ID, m.NAME, p.PHONENUMBER, p.PHONETYPE,
      ROW_NUMBER() OVER (PARTITION BY m.ID ORDER BY p.ID) RN
   FROM #USERMASTER m 
   JOIN #USERPHONE p ON m.ID = p.ID
) t
WHERE t.RN = 1