There is such a requirement, a form main table, an extended list, query to expand the list of multi-row into the main table multi-column.

such as

dt_zhubiao [main table]

dt_kuozhanbiao [Extended Table]

When querying, it will query according to the type of dt_zhubiao table. When the type field is the same, the number of dt_kuozhanbiao table bars and the name will be the same, and the value is not the same.

The desired results are as follows:

When querying type=1, select * from dt_zhubiao where type=1...

When querying type=2, select * from dt_zhubiao where type=2...

So the question arises, based on select * from dt_zhubiao where type = basic sql statement, how to generate such query results?  

This problem should be mostly used in dynamic forms. I have tried join and union to solve this problem before, which is almost meaningful. -)

Later, I went to several forums to post one after another, and finally found a solution. The SQL code is as follows:

--test data
if not object_id(N'Tempdb..#Master table') is null
    drop table #Master table
Go
Create table #Master table([id] int,[type] int,[title] nvarchar(25))
Insert #Master table
select 1,1,N'Form 1-1' union all
select 2,1,N'Form 1-2' union all
select 3,2,N'Form 2-1' union all
select 4,2,N'Form 2-2'
GO
if not object_id(N'Tempdb..#Extended table') is null
    drop table #Extended table
Go
Create table #Extended table([id] int,[formid] int,[name] nvarchar(26),[title] nvarchar(23),[value] nvarchar(22))
Insert #Extended table
select 1,1,N'ext_a',N'Working years',N'18' union all
select 2,1,N'ext_b',N'Title',N'Secondary level' union all
select 3,2,N'ext_1',N'Working years',N'18' union all
select 4,2,N'ext_b',N'Title',N'Positive grade' union all
select 5,3,N'ext_2a',N'Field 1',N'Value 1' union all
select 6,3,N'ext_2b',N'Field 2',N'Value 2' union all
select 7,3,N'ext_2c',N'Field 3',N'Value 3'union all
select 8,4,N'ext_2a',N'Field 1',N'Value 1' union all
select 9,4,N'ext_2b',N'Field 2',N'Value 2' union all
select 10,4,N'ext_2c',N'Field 3',N'Value 3'
Go
--End of test data
DECLARE @sql VARCHAR(MAX)
SET @sql = 'select #Master table.id,#Master table.type,#Master table.title'
SELECT  @sql = @sql + ',max(case name when ''' + name
        + ''' then [value] else null end)[' + name + ']'
FROM    ( Select DISTINCT name from #Extended table JOIN #Master table ON  formid IN (SELECT id FROM #Master table WHERE type=2)
        ) a
SET @sql = @sql
    + ' from #Extended table JOIN #Master table ON  formid =#Master table.id  WHERE type=2 group by #Master table.id,#Master table.type,#Master table.title'
EXEC(@sql) 

So far, I have solved my problem, but there is still something missing for the actual project application, such as paging, conditional screening. So I have to modify it a little bit on this basis. I will do this simple myself.

DECLARE @sql VARCHAR(MAX)
SET @sql = 'with tb as (select ROW_NUMBER() OVER(Order by  #Master table.id ) AS rindex,#Master table.id,#Master table.type,#Master table.title'
SELECT  @sql = @sql + ',max(case name when ''' + name
        + ''' then [value] else null end)[' + name + ']'
FROM    ( Select DISTINCT name from #Extended table JOIN #Master table ON  formid IN (SELECT id FROM #Master table WHERE type=2)
        ) a
SET @sql = @sql
    + ' from #Extended table JOIN #Master table ON  formid =#Master table.id  WHERE type=2 group by #Master table.id,#Master table.type,#Master table.title); select * from tb where ext_2b =''Value 1'' and rindex between  1 and 10 '  --Splicing with And query conditions
EXEC(@sql) 

By this time, I think it's almost possible to change it into a stored procedure, but let's think about using the program to implement it again and see which one is more convenient to implement -:)

There are two ways to implement the program, one is to assemble tables: first, to find the data set of the main table needed by paging, conditional filtering, and so on, to get a datatable, then to add corresponding columns to the DataTable dynamically, and then to assign values circularly (in this case, it is impossible to sort the extended fields).

DataTable tb = DbHelperSQL.Query("with tb as (select row_number over(order by #Master table.id) as rindex,* from #Master table where id in (select formid from #Extended table where value ='Value 2') adn type=2 );select * from tb where rindex between 1 and 10").Tables[0];
var ar = new System.Collections.ArrayList();
foreach (DataRow crow in DbHelperSQL.Query("select name from #Extended table where formid in (select id from #Master table where type = 2)").Tables[0].Rows) {
    ar.Add(crow[0]);
    tb.Columns.Add(crow[0].ToString(), typeof(string));
}
//Here you can load it all at once. tb All contained in the data set#Expand table data and then assign it in memory. The following loops query database assignment just for writing convenience
for (var i =0; i <tb.Rows.Count;i++) {
    var formid = tb.Rows[i]["id"];
    foreach (var ari in ar) {
        tb.Rows[i][ari.ToString()] = DbHelperSQL.GetSingle(string.Format("select value from #cursor where formid ={0} and name ='{1}'",formid,ari));
    }
 }
 return tb;

The second is to assemble the SQL statements, but not the @sql string before the program assembly, but still refer to the previous ideas of the SQL code, and then turn to the idea of the program code. In the previous SQL implementation code, the last exec(@sql) line plus the statement printing @sql will get the last SQL statement executed.

select #Master table.id,#Master table.type,#Main table.title
,max(case name when 'ext_2a' then [value] else null end)[ext_2a]
,max(case name when 'ext_2b' then [value] else null end)[ext_2b]
,max(case name when 'ext_2c' then [value] else null end)[ext_2c]
 from #Extended table JOIN #Master table ON  formid =#Master table.id  WHERE type=2 group by #Master table.id,#Master table.type,#Main table.title

Based on this SQL statement, the final SQL statement execution (including paging and conditional filtering) is spliced by program.

string sql=" with tb as (select row_number over(order by #Master table.id) rindex, #Master table.id,#Master table.type,#Master table.title";
foreach (DataRow crow in DbHelperSQL.Query("select name from #Extended table where formid in (select id from #Master table where type = 2) group by name").Tables[0].Rows) {
    sql +=",max(case name when '"+crow["name"]+"' then [value] else null end) "+crow["name"];
}
sql+=" from #Extended table join #Master table on formid = #Master table.id where type = 2 group by #Master table.id,#Master table.type,#Master table.title); select * from tb where ext_2a ='Value 1' and rindex between 1 and 10 ";
return DbHelperSQL.Query(sql).Tables[0];
//Here it's simple. SQL Statement splicing can also be re-optimized to achieve dynamic parameterization.

In this case, all columns can be sorted. At present, it is only to achieve this function, without considering performance optimization and so on.

Forum original advisory posts: https://bbs.csdn.net/topics/392999794  . A good memory is better than a bad pen. -)