获取带有值的SQL Server列名称

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

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 / *的调用将下的所有节点检索为派生表。其余的相当简单XQuery。

#2

Try this:

SELECT * FROM saverTbl 
WHERE LINK_userTblID = @val1
FOR JSON PATH

#1