Best way in SQL to store Name of Column along with the value of a particular row dynamically within a Stored Procedure in MS SQL Server? [duplicate]

yjghlzjz 于 2023-06-21 发布在 SQL Server

关注(0)|答案(1)|浏览(141)

This question already has answers here:

Unpivot with column name (3 answers)
Closed 4 days ago.

I want to retrieve the name and value of each column in a SQL table for a particular record, and store them as a separate entry in a different table using a stored procedure. I have tried using an INSERT EXEC statement within a loop, but I get an error saying that it cannot be nested within a stored procedure. How can I achieve this dynamic column name/value pairing in MS SQL Server?

For example, given the following table:
| Id | Name | Place |
| ------------ | ------------ | ------------ |
| 1 | 'Jane' | 'USA' |
| 2 | 'Tyrion' | 'UK' |

I want to retrieve the data for a specific Id (e.g. 1) and store the column name and value as separate entries in a different table like this:

Column Name	Column Value
Id	1
Name	'Jane'
Place	'USA'

Please suggest a solution or workaround.

sql-server

来源：https://stackoverflow.com/questions/76489579/best-way-in-sql-to-store-name-of-column-along-with-the-value-of-a-particular-row

1条答案

按热度按时间

k4ymrczo1#

Here is an option that will dynamically UNPIVOT virtually any table, view or query WITHOUT enumerating columns or using Dynamic SQL

Example

Select A.[Id]
      ,Item  = B.[Key]
      ,Value = B.[Value]
 From  YourTable A
 Cross Apply ( Select * 
                From  OpenJson((Select A.* For JSON Path,Without_Array_Wrapper )) 
                Where [Key] not in ('Id','Other','Columns','ToExclude')
             ) B

Results

Id  Item    Value
1   Name    Jane
1   Place   USA
2   Name    Tyrion
2   Place   UK

UPDATE ... XML Version

Select A.Id
      ,C.Item
      ,C.Value
 From YourTable A
 Cross Apply ( values (cast((Select A.* for XML RAW) as xml))) B(XMLData)
 Cross Apply (
               Select Item  = xAttr.value('local-name(.)', 'varchar(100)')
                     ,Value = xAttr.value('.','varchar(max)')
                From  xmlData.nodes('//@*') xNode(xAttr)
                 Where xAttr.value('local-name(.)','varchar(100)') Not in  ('Id')
             ) C

赞(0）回复(0）举报 2023-06-21

我来回答

Best way in SQL to store Name of Column along with the value of a particular row dynamically within a Stored Procedure in MS SQL Server? [duplicate]

1条答案

相关问题

热门标签

最新问答