Menggunakan fungsi bawaan SQL 2016 JSON

Tabel: Kolom Pengguna (ID int, Nama Depan varchar(50), Nama Belakang varchar(50)

Data 3009 Emily Manners 3010 Joanne Hernandez 3011 Kelly Kleiner 3012 Alexis Frederick 3013 Dieter Singleton 3018 Ashley Ely 3021 Jeralynn Campbell-Triplett 3026 Lauren Zinnerman 3027 Christopher Correa 3028 Stefanie MontalvoCruz

Hasil yang diinginkan -> tabel temp

ID       JSON_DATA        
3009    {"ID":3009,"FirstName":"Emily","LastName":"Manners"}
3010    {"ID":3010,"FirstName":"Joanne","LastName":"Hernandez"}

Mencoba menggunakan:

SELECT
    [ID],
    [FirstName],
    [LastName]
FROM Emp
FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER
1
DennisT 15 Maret 2017, 20:39

2 jawaban

Jawaban Terbaik

Dengan bantuan CROSS APPLY

Declare @Emp table (ID int,FirstName varchar(50),LastName varchar(50))
Insert into @Emp values
(3009,'Emily' ,'Manners'),
(3010,'Joanne','Hernandez'),
(3011,'Kelly' ,'Kleiner')

Select A.ID
      ,B.JSON_Data
 From @Emp A
 Cross Apply (
               Select JSON_Data = (Select A.ID,A.FirstName,A.LastName FOR JSON PATH, INCLUDE_NULL_VALUES, WITHOUT_ARRAY_WRAPPER)
             ) B

Kembali

ID      JSON_Data
3009    {"ID":3009,"FirstName":"Emily","LastName":"Manners"}
3010    {"ID":3010,"FirstName":"Joanne","LastName":"Hernandez"}
3011    {"ID":3011,"FirstName":"Kelly","LastName":"Kleiner"}
3
John Cappelletti 15 Maret 2017, 18:25

Formulir ini mungkin lebih mudah dibaca daripada menggunakan penerapan silang dalam beberapa keadaan.
Dalam kasus saya sudah mencoba rencana eksekusi selalu identik tetapi satu mungkin tampil lebih baik daripada yang lain dalam situasi tertentu.

Declare @Emp table (ID int,FirstName varchar(50),LastName varchar(50))
Insert into @Emp values
(3009,'Emily' ,'Manners'),
(3010,'Joanne','Hernandez'),
(3011,'Kelly' ,'Kleiner')

Select A.ID
      ,(Select A.ID,A.FirstName,A.LastName 
           FOR JSON PATH, INCLUDE_NULL_VALUES, 
           WITHOUT_ARRAY_WRAPPER) AS JSON_Data
 From @Emp A
0
bielawski 31 Mei 2017, 14:50