-1

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.

Yash Dhanwantri
  • 33
  • 1
  • 7
  • 2
    You need to unpivot your data. Honestly, this type of work is best done in the application layer, not the SQL layer. – Thom A Jun 16 '23 at 11:09
  • If your column names are fixed than why not just use union all like `SELECT 'Id' AS [Column Name],Id AS [Column Value] FROM YourTable WHERE Id=1 UNION ALL SELECT 'Name' AS [Column Name],Name AS [Column Value] FROM YourTable WHERE Id=1 UNION ALL SELECT 'Place' AS [Column Name],Place AS [Column Value] FROM YourTable WHERE Id=1` – Sandeep Jun 16 '23 at 11:17
  • @ThomA I agree, but I need this within a Stored Procedure, hence cannot do it in application layer. I'll see if unpivot helps. – Yash Dhanwantri Jun 16 '23 at 11:18
  • That's unlikely to be as performant as an unpivot, @Sandeep . Such a solution would result in multiple seeks (assuming a valid index) on the table, rather than a single one. – Thom A Jun 16 '23 at 11:18
  • 2
    *"I need this within a Stored Procedure"* Why do you "need" it in an procedure? – Thom A Jun 16 '23 at 11:19
  • In a stored procedure? Simply fetch the row, and then do what you need with the values you've got. (Similar as you would have done in the application layer.) – jarlh Jun 16 '23 at 11:35

1 Answers1

1

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
John Cappelletti
  • 79,615
  • 7
  • 44
  • 66