Multi Row Table to Key - Value Pair

Question

Hi There,

I have a dynamic query that returns a table with few rows but this result set has dynamic number of columns and names. Like,

Like, resultset with 2 column here
StudentID - StudentName
1 - Johm
2 - Patrick
3 - Bob

Or The resultset could be with 1 column
Dept.
Maths
English

Now we'd like to convert and store this data into a Key-Value Pair table as below

RowID - Key - Value
1 StudenID 1
1 StudentName John
2 StudentID 2
2 StudentName Patrick
3 StudentID 3
3 StudentName Bob
4 Dept Maths
5 Dept English

We tried with XML path, browsing node, etc, but couldn't add this RowID type of field which establishes the link between rows... i.e. ROWID1 for two rows in my target should tell me that there were Student ID 1 and his name is John.

Is there a way please?

Answer 1

Hi @Avik Mukherjee ,

Welcome to Microsoft Q&A!

I created two tables including one external id column to store the two resultsets and another table Pair to store the final result.

Please refer below and check whether it is helpful to you.

--DDL  
create table t1   
(id int identity(1,1) ,  
StudentID int,  
 StudentName varchar(20))  
  
 insert into t1 values  
 (1,'Johm'),  
 (2,'Patrick'),  
 (3,'Bob')  
  
 create table t2   
(id int identity(1,1) ,  
Dept  varchar(20))  
  
insert into t2 values  
('Maths'),  
('English')  
  
create table Pair   
(RowID int,  
[Key] varchar(20),  
[Value] varchar(20))  

Then create one procedure as below:

create or alter procedure myprocedure (@TableName varchar(100))  
as  
begin  
	DECLARE @sql nVARCHAR(max), @max int  
	select @max=isnull(max(rowid),0) from pair  
	select @sql  =''  
	select  @sql = @sql+ ' Select  id+'+cast(@max as char(2))+','''+ [name] +' '' [key], cast(' + [name]  + ' as nvarchar(10)) [Value] from  ' +@TableName + ' union '  
			 from sys.columns where object_name (object_id) = @TableName and [name]<>'id'  
  
	set @sql = SUBSTRING(@sql,1,len(@sql)-5) + '  order by id+'+cast(@max as char(2))  
	insert into Pair  
	exec(@sql)   
end  

Then execute this procedure as below:

exec myprocedure 't1'  
exec myprocedure 't2'  

Finally query the Pair table:

select * from Pair  

Output:

RowID	Key	Value  
1	StudentID 	1  
1	StudentName 	Johm  
2	StudentID 	2  
2	StudentName 	Patrick  
3	StudentID 	3  
3	StudentName 	Bob  
4	Dept 	Maths  
5	Dept 	English  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thanks for your comment.
But I'd like to better clarify when I mentioned - "Dynamic Query that returns a Table" - here I meant a Typical Program that does some logical operation and finally returns a Resultset with a Variable Number of Columns. NO Physical Table I'm referring to.

So the whole approach of looking into [Sys. Column] etc. doesn't work for me. All I need is a kind of Wrapper on top of my Dynamic Query.

What we tried is the following

select T2.N.value('local-name(.)', 'nvarchar(128)') as [Key],
T2.N.value('text()[1]', 'nvarchar(max)') as Value
from (select * from (<<< My Query that Returns Variable number of Columns >>> ) T for xml path(''), type) as T1(X)
cross apply T1.X.nodes('/*') as T2(N)

This indeed converts data into Key-Value pair but the only problem is associating an ID column, so we can identify which rows in the Key-Value Pair resultset belong to the same row of the source query.

Answer 3

In case of dynamic queries with variable number of columns, like the next sample two-column @dynamic _query, try this technique:

declare @dynamic_query as varchar(max)  
  
set @dynamic_query = '  
    select 1 as StudentID, ''John'' as StudentName  
    union all  
    select 2 as StudentID, ''Patrick'' as StudentName  
    union all  
    select 3 as StudentID, ''Bob'' as StudentName  
'  
  
declare @q as nvarchar(max) = concat(' ; with Q1 as ( ', @dynamic_query, '), ',  
    'Q2 as ( select *, row_number() over(order by (select 0)) as rn from Q1 ) ' )  
  
declare @s as nvarchar(max) =  
(   
    select string_agg( concat( 'select rn as RowID, ', quotename([name], ''''), ' as [Key], cast(', quotename([name]), 'as varchar(max)) as [Value] from Q2'), ' union all ')  
    from sys.dm_exec_describe_first_result_set(@dynamic_query, null, 0)  
)  
  
set @q = concat(@q, @s, ' order by RowID, [Key]')  
  
exec (@q)  

Then you can use INSERT…EXEC to store the rows. You can easily adjust this script to continue numbering from the latest (maximum) RowID if the key-value table already contains some data. Therefore, RowID is not necessarily the StudentID, but it allows you to link the rows and determine the StudentID of John, for example.

Answer 4

Hi @Avik Mukherjee ,

Thanks for your update.

Please refer below and check whether it is helpful.

declare  @t1  table  
 (StudentID int,  
  StudentName varchar(20))  
      
insert into @t1 values  
  (1,'Johm'),  
  (2,'Patrick'),  
  (3,'Bob')  
  
declare @XML xml  
  
select @xml=  
(select * from @t1  
for xml path('') )  
  
;with cte as(  
Select   
[key] = x.value('local-name(.)','varchar(150)')  
,Value = x.value('text()[1]','varchar(max)')  
,Seq = cast(Row_Number() over(Order By (Select 1)) as varchar(max))  
From  @XML.nodes('/*') a(x) )  
select ROW_NUMBER() over (partition by [key] order by Seq) rowid,  
[key],value from cte  
order by seq  

Output:

rowid	key	value  
1	StudentID	1  
1	StudentName	Johm  
2	StudentID	2  
2	StudentName	Patrick  
3	StudentID	3  
3	StudentName	Bob  

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.