I am trying to create a query that allows me to have the column name next to its column values that it finds.
我正在尝试创建一个查询,允许我将列名称放在它找到的列值旁边。
As an example:
举个例子:
SELECT
*
FROM
INFORMATION_SCHEMA.COLUMNS
WHERE
TABLE_NAME = 'saverTbl'
The above returns all of the column names in that table like this:
上面返回该表中的所有列名称,如下所示:
id, Link_userTblID, type, environment, vendor, theGUID, theGUID, etc etc...
Now what I need is to get the output from the table name next to the value. This is my query to just get my values from the table:
现在我需要的是从值旁边的表名获取输出。这是我从表中获取值的查询:
SELECT
*
FROM
saverTbl
WHERE
LINK_userTblID = @val1
The above returns the row that matches the LINK_userTblID
like so:
以上返回与LINK_userTblID匹配的行,如下所示:
32, 1, 'Blah', 'something', 'Adobe', 546656156-45332-54616516-4515, etc etc..
Now putting that all together (which is what this question is all about):
现在把它们放在一起(这就是这个问题的全部内容):
id: 32,
LINK_userTblID: 1,
type: Blah,
environment: something,
vendor: Adobe,
theGUID: 546656156-45332-54616516-4515,
etc etc.....
Pretty much needing the output in a json format but the column name matching up with the columns value.
非常需要以json格式输出,但列名与列值匹配。
2 个解决方案
#1
1
Assuming, this is meant for one single row you can try this:
假设这是一行,你可以试试这个:
WITH cte AS
(
SELECT
(SELECT TOP 1 * FROM sys.objects FOR XML PATH('row'),TYPE) AS TheXml
)
SELECT TheElement.value('local-name(.)','nvarchar(max)')
+ ': '
+ TheElement.value('text()[1]','nvarchar(max)') AS YourOutput
FROM cte
CROSS APPLY TheXml.nodes('/row/*') AS A(TheElement);
The result:
YourOutput
---------------
name: sysrscols
object_id: 3
schema_id: 4
parent_object_id: 0
type: S
type_desc: SYSTEM_TABLE
create_date: 2012-02-10T20:15:58.693
modify_date: 2012-02-10T20:15:58.700
is_ms_shipped: 1
is_published: 0
is_schema_published: 0
XML in connection with XQuery
and XPath
is a very mighty toolset to solve rather generic problems. The cte builds an XML which looks like this:
与XQuery和XPath相关的XML是一个非常强大的工具集,可以解决相当普遍的问题。 cte构建一个如下所示的XML:
<row>
<name>sysrscols</name>
<object_id>3</object_id>
<schema_id>4</schema_id>
<parent_object_id>0</parent_object_id>
<type>S </type>
<type_desc>SYSTEM_TABLE</type_desc>
<create_date>2012-02-10T20:15:58.693</create_date>
<modify_date>2012-02-10T20:15:58.700</modify_date>
<is_ms_shipped>1</is_ms_shipped>
<is_published>0</is_published>
<is_schema_published>0</is_schema_published>
</row>
The call to /row/*
retrieves all nodes below <row>
as derived table. The rest is rather easy XQuery
.
对/ row / *的调用将
#2
0
Try this:
SELECT * FROM saverTbl
WHERE LINK_userTblID = @val1
FOR JSON PATH
#1
1
Assuming, this is meant for one single row you can try this:
假设这是一行,你可以试试这个:
WITH cte AS
(
SELECT
(SELECT TOP 1 * FROM sys.objects FOR XML PATH('row'),TYPE) AS TheXml
)
SELECT TheElement.value('local-name(.)','nvarchar(max)')
+ ': '
+ TheElement.value('text()[1]','nvarchar(max)') AS YourOutput
FROM cte
CROSS APPLY TheXml.nodes('/row/*') AS A(TheElement);
The result:
YourOutput
---------------
name: sysrscols
object_id: 3
schema_id: 4
parent_object_id: 0
type: S
type_desc: SYSTEM_TABLE
create_date: 2012-02-10T20:15:58.693
modify_date: 2012-02-10T20:15:58.700
is_ms_shipped: 1
is_published: 0
is_schema_published: 0
XML in connection with XQuery
and XPath
is a very mighty toolset to solve rather generic problems. The cte builds an XML which looks like this:
与XQuery和XPath相关的XML是一个非常强大的工具集,可以解决相当普遍的问题。 cte构建一个如下所示的XML:
<row>
<name>sysrscols</name>
<object_id>3</object_id>
<schema_id>4</schema_id>
<parent_object_id>0</parent_object_id>
<type>S </type>
<type_desc>SYSTEM_TABLE</type_desc>
<create_date>2012-02-10T20:15:58.693</create_date>
<modify_date>2012-02-10T20:15:58.700</modify_date>
<is_ms_shipped>1</is_ms_shipped>
<is_published>0</is_published>
<is_schema_published>0</is_schema_published>
</row>
The call to /row/*
retrieves all nodes below <row>
as derived table. The rest is rather easy XQuery
.
对/ row / *的调用将
#2
0
Try this:
SELECT * FROM saverTbl
WHERE LINK_userTblID = @val1
FOR JSON PATH