问专用Json输出格式

EN

CREATE TABLE V (Col1 varchar(10), Col2 varchar(10),Col3 varchar(10));
INSERT INTO V    
SELECT *
FROM (VALUES('value1','value2','value3'),
            ('value1','value2','value3')) V(Col1, Col2, Col3);

DECLARE @JSON nvarchar(MAX);

SET @JSON = N'{' + NCHAR(13) + NCHAR(10) +
            N'   "data": [' + NCHAR(13) + NCHAR(10) +
            N'       [' + STUFF((SELECT N', ' + QUOTENAME(c.[name],'"')
                                 FROM sys.columns c
                                      JOIN sys.tables t ON c.object_id = t.object_id
                                 WHERE t.[name] = N'V'
                                 ORDER BY c.column_id ASC
                                 FOR XML PATH(N''),TYPE).value('.', 'varchar(MAX)'),1,2,N'') + N'],' + NCHAR(13) + NCHAR(10) +
            STUFF((SELECT N',' + NCHAR(13) + NCHAR(10) +
                          N'       [' + QUOTENAME(Col1,'"') + N', ' + QUOTENAME(Col2,'"') + N', ' + QUOTENAME(Col3,'"') + N']'
                   FROM V
                   ORDER BY V.Col1
                   FOR XML PATH(N''),TYPE).value('.', 'varchar(MAX)'),1,3,N'') + NCHAR(13) + NCHAR(10) +
            N'    ]' + NCHAR(13) + NCHAR(10) +
            N'}';

PRINT @JSON;

DROP TABLE V;