在SQL Server 2016中将表转换为JSON每列值

Liv 发表于 Dev

生活

我有下表：

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)

我需要编写一个查询，customerId在此表中，每个查询都以json字符串结尾。

到目前为止，我有

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

但我想将所有这些结合起来，所以最终只剩下3行。

对于customerId 2，结果将是

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

如果可能的话，我希望能够通过storeCity然后transactionDate对json字符串中的行进行排序。

谁能帮我这个？

提前致谢。

米恰尔·图尔钦

试试这个：

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

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。