SQL Server: How to query data all rows as Json object into next to other columns?

Question

I have data like this:

I want to query result like this:

Here is my code

SELECT
     PML_CODE
    ,PML_NAME_ENG
    ,(
        SELECT
              PML_ID
             ,PML_NO
             ,PML_CODE
             ,PML_NAME_ENG
             ,PML_FORMULA
        FROM DSP.PARAMET_LIST AS A WITH(NOLOCK)
        WHERE A.PML_ID = B.PML_ID 
        FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    ) AS BR_OBJECT
FROM DSP.PARAMET_LIST AS B WITH(NOLOCK)

My code works for what I want, but I want to know if there is a better, faster way to write this query?

See Bad Habits to kick - putting NOLOCK everywhere - it is not recommended to use this everywhere - quite the contrary! — marc_s
– marc_s, Commented Jun 11, 2019 at 3:50
@marc_s My boss recommend me to use WITH(NOLOCK) every SELECT statement. — Sophairk
– Sophairk, Commented Jun 11, 2019 at 4:22
Tell your boss to read that article I linked to - using WITH (NOLOCK) everywhere is a horribly bad idea and usually the sign of design flaws in your database — marc_s
– marc_s, Commented Jun 11, 2019 at 4:30
FYI, blogs.sqlsentry.com appears to be directed to a WP host site that no longer hosts/ recognizes that site — user1664043
– user1664043, Commented Oct 31, 2024 at 20:43

Gottfried Lesigang · Accepted Answer · 2019-06-12 10:22:02Z

Next time please do not post pictures, but rather try to create some DDL, fill it with sample data and state your own attempts and the expected output. This makes it easier for us to understand and to answer your issue.

You can try it like this:

DECLARE @tbl TABLE(PML_ID BIGINT, PML_NO INT, PML_CODE VARCHAR(10), PML_NAME_ENG VARCHAR(10), PML_FORMULA VARCHAR(10));
INSERT INTO @tbl VALUES
 (2017102600050,1,'KHR','Riel','01')
,(2017102600051,2,'USD','Dollar','02')
,(2017102600052,3,'THB','Bath','05')

SELECT
     PML_CODE
    ,PML_NAME_ENG
    ,BR_OBJECT
FROM @tbl
CROSS APPLY(
    SELECT
    (
            SELECT
                  PML_ID
                 ,PML_NO
                 ,PML_CODE
                 ,PML_NAME_ENG
                 ,PML_FORMULA
            FOR JSON PATH, WITHOUT_ARRAY_WRAPPER
    )) AS A(BR_OBJECT);

The big difference to your own approach is that I use a CROSS APPLY using the columns we have already instead of calling a correlated sub-query.

gotqn · Accepted Answer · 2019-06-11 05:30:36Z

You can just concatenate the values. Be sure to cast the integers and to handle the NULL values. For example, if there is NULL value for column, there can be two cases - ignore the property or add the property with null, right?

For SQL Server 2016 SP1+ and later you can use FOR JSON. Basically, you should end up with something like this:

DECLARE @DataSource TABLE
(
    [PML_ID] VARCHAR(64)
   ,[PML_NO] INT
   ,[PML_CODE] VARCHAR(3)
   ,[PML_NAME_ENG] NVARCHAR(32)
   ,[PML_FORMULA] VARCHAR(2)
);

INSERT INTO @DataSource ([PML_ID], [PML_NO], [PML_CODE], [PML_NAME_ENG], [PML_FORMULA])
VALUES ('201710260000000050', 1, 'KHR', 'Riel', 01)
      ,('201710260000000051', 2, 'USD', 'Dollar', 02)
      ,('201710260000000052', 3, 'THB', 'Bath', 05);

SELECT [PML_CODE]
      ,[PML_NAME_ENG]
      ,'{"PML_ID":'+ [PML_ID] +',"PML_NO":'+ CAST([PML_NO] AS VARCHAR(12)) +',"PML_CODE":'+ [PML_CODE] +',"PML_NAME_ENG":'+ [PML_NAME_ENG] +',"PML_FORMULA":'+ [PML_FORMULA] +'}' AS [BR_OBJECT]
FROM @DataSource;

-- SQL Server 2016 SP1 and latter
SELECT DS1.[PML_CODE]
      ,DS1.[PML_NAME_ENG]
      ,DS.[BR_OBJECT]
FROM @DataSource DS1
CROSS APPLY
(
    SELECT *
    FROM @DataSource DS2
    WHERE DS1.[PML_CODE] = DS2.[PML_CODE]
      AND DS2.[PML_NAME_ENG] = DS2.[PML_NAME_ENG]
    FOR JSON AUTO
) DS ([BR_OBJECT]);

Collectives™ on Stack Overflow

SQL Server: How to query data all rows as Json object into next to other columns?

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related