SQL Query to Transpose dynamic Rows to Column

Question

User-1641476077 posted

Below is a sample of my schema design:

<script async="" src="//s.imgur.com/min/embed.js" charset="utf-8"></script>

From the design above, different form might have different template id, I am wondering how do I write a generic query to transpose the rows in TemplateColumn to table column.

i am reading a guide from here but am still unsure how to implement the query for my sceanrio

Sample of final result I wanted as below

template 1	template 2	template 3	template 4	template 5	template 6	template 7	template 8	template 9	template 10
response_row1, response_col1	response_row1, response_col2	response_row1, response_col3	response_row1, response_col4	response_row1, response_col5	response_row1, response_col6	response_row1, response_col7	response_row1, response_col8	response_row1, response_col9	response_row1, response_col10
response_row2, response_col1	response_row2, response_col2	response_row2, response_col3	response_row2, response_col4	response_row2, response_col5	response_row2, response_col6	response_row2, response_col7	response_row2, response_col8	response_row2, response_col9	response_row2, response_col10
response_row3, response_col1	response_row3, response_col2	response_row3, response_col3	response_row3, response_col4	response_row3, response_col5	response_row3, response_col6	response_row3, response_col7	response_row3, response_col8	response_row3, response_col9	response_row3, response_col10
response_row4, response_col1	response_row4, response_col2	response_row4, response_col3	response_row4, response_col4	response_row4, response_col5	response_row4, response_col6	response_row4, response_col7	response_row4, response_col8	response_row4, response_col9	response_row4, response_col10
response_row5, response_col1	response_row5, response_col2	response_row5, response_col3	response_row5, response_col4	response_row5, response_col5	response_row5, response_col6	response_row5, response_col7	response_row5, response_col8	response_row5, response_col9	response_row5, response_col10

Answer 1

User1535942433 posted

Hi bczm8703,

How do you design the sql table? What's result do you need?

I just only tell you that you need "pivot"  according to your post to us currently. Could you post us more details?It will help us to solve your problems and give the codes you want.

https://www.sqlshack.com/dynamic-pivot-tables-in-sql-server/

Best regards,

Yijing Sun

Answer 2

User1108898723 posted

Sample of final result I wanted as below

It makes sense to load whole dataset (as it seems number of rows is rather limited) and prepare pivot table on .NET side.

This could be easily done with help of NReco.PivotData library (it is open source https://github.com/nreco/pivotdata) where you can define even custom 'aggregate' function and get pivot table cell's contents as you need. Then, you can use PivotTable class to get a data model in terms of rows/columns and easily render HTML <table> simply with iterating through rows and columns.