SQL Query to Transpose dynamic Rows to Column RRS feed

  • Question

  • User-1641476077 posted

    Below is a sample of my schema design:

    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

    Tuesday, May 4, 2021 9:41 AM

All replies

  • 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.


    Best regards,

    Yijing Sun

    Wednesday, May 5, 2021 5:15 AM
  • 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. 

    Wednesday, June 16, 2021 8:07 AM