Convert table into JSON per column value in SQL Server 2016

Question

I have the following table:

CREATE TABLE #Temp
(
    customerId INT
  , storeCity VARCHAR(50)
  , transactionDate VARCHAR(100)
  , TransactionDescription VARCHAR(200)
  , Amount DECIMAL(18, 2)
)

INSERT INTO #Temp (customerId, storeCity, transactionDate, TransactionDescription, Amount)
VALUES (2, 'Neuwied', 'January 14th, 2018', 'Lorem ipsum dolor', 278),
       (1, 'Sunset Point', 'September 14th, 2018', 'Lorem ipsum dolor sit amet, consectetuer', 159),
       (1, 'Celle', 'March 18th, 2018', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur', 47),
       (3, 'General Lagos', 'March 27th, 2018', 'Lorem', 433), 
       (2, 'Ekeren', 'January 16th, 2018', 'Lorem ipsum dolor sit amet, consectetuer adipiscing', 308),
       (3, 'Montreal', 'November 24th, 2018', 'Lorem', 406),
       (1, 'Hamilton', 'March 17th, 2018', 'Lorem ipsum dolor sit amet, consectetuer adipiscing elit. Curabitur sed', 240)

I need to write a query where I end up with a json string for each customerId in this table.

So far I have

SELECT   
    customerId,
    (SELECT 
         storeCity,
         transactionDate,
         TransactionDescription
     FOR JSON PATH, INCLUDE_NULL_VALUES)
FROM     
    #Temp
ORDER BY 
    1

but I'd like to combine all these so I end up with 3 rows only.

For customerId 2, the result would be

[
  {
    "storeCity": "Neuwied",
    "transactionDate": "January 14th, 2018",
    "TransactionDescription": "Lorem ipsum dolor"
  },
  {
    "storeCity": "Ekeren",
    "transactionDate": "January 16th, 2018",
    "TransactionDescription": "Lorem ipsum dolor sit amet, consectetuer adipiscing"
  }
]

If possible, I'd like to be able to have the rows within the json string be sorted by storeCity and then transactionDate.

Can anyone help me with this?

Thanks in advance.

score 2 · Accepted Answer · answered Feb 08 '18 at 07:57

Try this:

select distinct customerId, (
                select StoreCity,
                       TransactionDate,
                       TransactionDescription
                from Temp
                where customerId = T.customerId
                for json path) [JSON]
from Temp T

Convert table into JSON per column value in SQL Server 2016

1 Answers1