question

AvikMukherjee-4726 avatar image
0 Votes"
AvikMukherjee-4726 asked MelissaMa-msft commented

Multi Row Table to Key - Value Pair

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?

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @AvikMukherjee-4726,

Could you please validate the latest answers so far and provide any update?

If all are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @AvikMukherjee-4726,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AvikMukherjee-4726 avatar image
0 Votes"
AvikMukherjee-4726 answered AvikMukherjee-4726 published

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered

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.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @AvikMukherjee-4726,

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.