Prepare LimeSurvey output with sql for Crystal Reports

Question

I’m working with Lime Survey and ultimately want to use Crystal Reports for my final output, and am looking for help with the intervening steps. I have one row per response record, with 100+ questions, which are split into several sections. The output looks like a cross-tab with a column per question, but I the data needs to be unpivoted before I can work with it in Crystal Reports.

Depending on the survey, there might be 4 sections or there could be as many as 15. So, is there a way to do this dynamically with sql, based on the number of sections?

To illustrate – in Excel the output from Lime Survey looks like this

ID  Subject Relationship    1Section    1SQuestion1 1SQuestion2 2Section    2SQuestion1 2SQuestion2
1   John    Boss            1Section    2           4           2Section    3           4
2   John    Peer            1Section    4           3           2Section    2           5
3   Sally   Boss            1Section    3           3           2Section    4           5
4   Sally   Peer            1Section    5           6           2Section    1           3

Here’s what I ultimately need it to look like

ID  Subject Relationship    1Section    Col5            Col6
1   John    Boss            1Section    1SQuestion1     2
1   John    Boss            1Section    1SQuestion2     4
2   John    Peer            1Section    1SQuestion1     4
2   John    Peer            1Section    1SQuestion2     3
3   Sally   Boss            1Section    1SQuestion1     3
3   Sally   Boss            1Section    1SQuestion2     3
4   Sally   Peer            1Section    1SQuestion1     5
4   Sally   Peer            1Section    1SQuestion2     6
1   John    Boss            2Section    2SQuestion1     3
1   John    Boss            2Section    2SQuestion2     4
2   John    Peer            2Section    2SQuestion1     2
2   John    Peer            2Section    2SQuestion2     5
3   Sally   Boss            2Section    2SQuestion1     4
3   Sally   Boss            2Section    2SQuestion2     5
4   Sally   Peer            2Section    2SQuestion1     1
4   Sally   Peer            2Section    2SQuestion2     3

Thanks

Answer 1

If you want to perform this data transformation in sql, then you can use a UNION ALL query:

select id, subject, relationship, `1Section`, '1sQuestion1' col5, `1sQuestion1` col6
from yourtable
union all
select id, subject, relationship, `1Section`, '1sQuestion2' col5, `1sQuestion2` col6
from yourtable
union all
select id, subject, relationship, `2Section`, '2sQuestion1' col5, `2sQuestion1` col6
from yourtable
union all
select id, subject, relationship, `2Section`, '2sQuestion2' col5, `2sQuestion2` col6
from yourtable

See SQL Fiddle with Demo. Which gives the result:

| ID | SUBJECT | RELATIONSHIP | 1SECTION |        COL5 | COL6 |
---------------------------------------------------------------
|  1 |    John |         Boss | 1Section | 1sQuestion1 |    2 |
|  2 |    John |         Peer | 1Section | 1sQuestion1 |    4 |
|  3 |   Sally |         Boss | 1Section | 1sQuestion1 |    3 |
|  4 |   Sally |         Peer | 1Section | 1sQuestion1 |    5 |
|  1 |    John |         Boss | 1Section | 1sQuestion2 |    4 |
|  2 |    John |         Peer | 1Section | 1sQuestion2 |    3 |
|  3 |   Sally |         Boss | 1Section | 1sQuestion2 |    3 |
|  4 |   Sally |         Peer | 1Section | 1sQuestion2 |    6 |
|  1 |    John |         Boss | 2Section | 2sQuestion1 |    3 |
|  2 |    John |         Peer | 2Section | 2sQuestion1 |    2 |
|  3 |   Sally |         Boss | 2Section | 2sQuestion1 |    4 |
|  4 |   Sally |         Peer | 2Section | 2sQuestion1 |    1 |
|  1 |    John |         Boss | 2Section | 2sQuestion2 |    4 |
|  2 |    John |         Peer | 2Section | 2sQuestion2 |    5 |
|  3 |   Sally |         Boss | 2Section | 2sQuestion2 |    5 |
|  4 |   Sally |         Peer | 2Section | 2sQuestion2 |    3 |