Linear SQL query without importing fields, is it possible?

Question

Linear SQL query without importing fields, is it possible?

Navigation

#1 by (0 votes)

1

Is it possible to make a query, which created columns automatically according to the data, regardless of the condition?

Let me explain:

I have a table of surveys, another table that contains the questions that are made by surveys and other the answers that are made to the questions by survey. This good, goes with the user who does it, who gets it done and so on ...

There is a table called Encuestas Realizadas , that's where they are saved. To put it in a certain way, transactions per survey is done.

When a survey is done, this table, and the answer table, are the ones that are filled out.

Now:

I need a report in which it is evidenced, by Transacción (survey that is carried out), the id of the survey, its name, question and answer.

So far everything is normal, it can be done with its select e inner joins , but now they tell me, Is it possible that the question-answer report is made horizontally and not vertically? .

That is:

The query I have already makes the report vertically:

ID_EncuestaRealizada | ID_Encu | Encuesta | Id Pregu | Pregunta | ID Res | Respues | Fecha

    00001 | 1 | Encuesta 1 | 1 | Pregunta 1 |  2 | Si | 2017-18-05 12:00:00
    00001 | 1 | Encuesta 1 | 2 | Pregunta 2 |  2 | Si | 2017-18-05 12:00:20
    00001 | 1 | Encuesta 1 | 3 | Pregunta 3 |  3 | No | 2017-18-05 12:00:49
    00002 | 3 | Encuesta 3 | 1 | Pregunta 1 |  1 | 5 | 2017-18-05 12:00:00
    00002 | 3 | Encuesta 3 | 2 | Pregunta 2 |  2 | Si | 2017-18-05 12:00:20
    00002 | 3 | Encuesta 3 | 3 | Pregunta 3 |  3 | No | 2017-18-05 12:00:49
    00002 | 3 | Encuesta 3 | 4 | Pregunta 4 |  2 | Si | 2017-18-05 12:00:49

Now, for me, I think it's impossible to show, in the case of this example, only two rows , since one, the questions are not the same and per survey There may be more or fewer questions .

I mean, they want more or less something like that.

00001 | 1 | Encuesta 1 | 1 | Pregunta 1 | 2 | Si | 2 | Pregunta 2 | 2 | Si | 3 | Pregunta 3 | 3 | No |        
00002 | 3 | Encuesta 3 | 1 | Pregunta 1 | 1 | 5 | 2  | Pregunta 2 | 2 | Si | 3 | Pregunta 3 | 3 | No | 4 | Pregunta 4 | 2 | Si |

SQL query I have

 SELECT 
 ENREA.Id_EncuestaRealizada
,ENREA.UserName
,EN.Id_Encuesta
,EN.Nombre
,ENRES.Id_Pregunta
,PRE.Pregunta
,ENRES.Id_Respuesta
,RESP.Respuesta
,ENRES.RespuestaNumerica
,ENRES.RespuestaNumerica
,ENRES.RespuestaAbierta
,ENRES.Comentarios
FROM [dbo].[tbl_encuestasrealizadas] ENREA
INNER JOIN [dbo].[tbl_encuestasrespuesta] ENRES ON ENRES.Id_EncuestaRealizada = ENREA.Id_EncuestaRealizada
INNER JOIN [dbo].[tbl_encuestas] EN ON EN.Id_Encuesta = ENREA.Id_Encuesta
INNER JOIN [dbo].[tbl_preguntas] PRE ON PRE.Id_Pregunta = ENRES.Id_Pregunta AND PRE.Id_Encuesta = ENREA.Id_Encuesta
LEFT JOIN [dbo].[tbl_respuestalistas] RESP ON RESP.Id_Respuesta = ENRES.Id_Respuesta
WHERE EN.Id_Campana = 1;

That gives me a table like that (For me, that's fine)

In this sample three different surveys, it may be that by coincidence some surveys have the same number of questions, but it is not exact, that is variable, can be n number of questions per survey . . p>

It's still impossible for me to do that.

sql sql-server

asked by Fabian Montoya 18.05.2017 в 19:28

source

1 answer

A UIButton can take the image of a UIImage Objective-C C # capture windows service messages

score 0 · Answer 1

I think you could use the function STUFF and FOR XML PATH in this way, I do not know if it's how you want it, but this is my idea:

SELECT
    STUFF(
    (SELECT 
        ', [ENCUESTA REALIZADA:' + ENREA.Id_EncuestaRealizada + ', USUARIO:' + ENREA.UserName + ', ENCUESTA:' + EN.Id_Encuesta + ']'
    FROM [dbo].[tbl_encuestasrealizadas] ENREA
    INNER JOIN [dbo].[tbl_encuestasrespuesta] ENRES ON ENRES.Id_EncuestaRealizada = ENREA.Id_EncuestaRealizada
    INNER JOIN [dbo].[tbl_encuestas] EN ON EN.Id_Encuesta = ENREA.Id_Encuesta
    INNER JOIN [dbo].[tbl_preguntas] PRE ON PRE.Id_Pregunta = ENRES.Id_Pregunta
        AND PRE.Id_Encuesta = ENREA.Id_Encuesta
    LEFT JOIN [dbo].[tbl_respuestalistas] RESP ON RESP.Id_Respuesta = ENRES.Id_Respuesta
    WHERE EN.Id_Campana = 1 AND ENREA.Id_EncuestaRealizada = ER.Id_EncuestaRealizada FOR XML PATH('')), 1, 1, '') AS RESULTADOS
FROM
    [dbo].[tbl_encuestasrealizadas] AS ER

I'm not sure if the query works, but it's my idea of what you need.