Multiple rows in a Sql Server column

Asked

Viewed 880 times

1

I have a problem where I need to join the return of a query made in two tables in a single row.

I thought I’d use PIVOT but I couldn’t because of INNER JOIN.

The consultation is like this:

SET LANGUAGE us_english; 
SELECT CONVERT(VARCHAR, ftd.DateAndTime, 113) dt, 
round(ftd.Val, 2) Val, ttd.TagName 
FROM FloatTableDiario ftd INNER JOIN TagTableDiario ttd 
ON ttd.TagIndex = ftd.TagIndex 
WHERE ftd.Marker = 'S' 
AND ftd.DateAndTime BETWEEN '05-29-2018 00:00:000' AND '05-29-2018 23:59:059' 
AND ttd.TagName 
IN('[AGUA]FT[0].Tot_NR', 
 '[AGUA]FT[0].Tot_DA', 
 '[AGUA]POCO[0].IntTotPrdHou', 
 '[AGUA]POCO[0].IntTotPrdMin', 
 '[AGUA]POCO[0].OutEfePrdHou_DA ', 
 '[AGUA]POCO[0].OutEfePrdMin_DA', 
 '[AGUA]LT_MAX_DA[0]', 
 '[AGUA]LT_MIN_DA[0]') 
ORDER BY ftd.DateAndTime

The return obviously comes in a different line to face value of the IN.

inserir a descrição da imagem aqui

I need the whole result to come in a single line, at each interval more than 400 data will be generated.

sql sql-server

  • Adiciona um group by pela data 

 SELECT CONVERT(VARCHAR, ftd.DateAndTime, 113) dt, round(ftd.Val, 2) Val, ttd.TagName FROM FloatTableDiario ftd INNER JOIN TagTableDiario ttd ON ttd.TagIndex = ftd.TagIndex WHERE ftd. Marker = ’S' AND ftd.Dateandtime BETWEEN '05-29-2018 00:00:000' AND '05-29-2018 23:59:059' AND ttd.Tagname IN('[AGUA]FT[0].Tot_NR', '[AGUA]FT[0].Tot_DA', '[AGUA]POCO[0].IntTotPrdHou', '[AGUA]POCO[0].IntTotPrdMin', '[AGUA]POCO[0].OutEfePrdHou_DA ', '[AGUA]POCO[0].OutEfePrdMin_DA', '[AGUA]LT_MAX_DA[0]', '[AGUA]LT_MIN_DA[0]') group by dt ORDER BY ftd.DateAndTime

  • Thanks for the reply, unfortunately not solved.

  • Take a look at this example https://stackoverflow.com/a/17073196/9916784 and see if this solves your problem.

1 answer

0


You can always try with a FOR XML:

SET LANGUAGE us_english

SELECT      (
                SELECT      CONVERT(VARCHAR, ftd.DateAndTime, 113)  + ';'
                            CONVERT(VARCHAR, ROUND(ftd.Val, 2))     + ';'
                            ttd.TagName
                FROM        FloatTableDiario    ftd 
                INNER JOIN  TagTableDiario      ttd ON ttd.TagIndex = ftd.TagIndex 
                WHERE       ftd.Marker = 'S' 
                        AND ftd.DateAndTime BETWEEN '05-29-2018 00:00:000' AND '05-29-2018 23:59:059' 
                        AND ttd.TagName IN  (       '[AGUA]FT[0].Tot_NR'
                                                ,   '[AGUA]FT[0].Tot_DA'
                                                ,   '[AGUA]POCO[0].IntTotPrdHou'
                                                ,   '[AGUA]POCO[0].IntTotPrdMin'
                                                ,   '[AGUA]POCO[0].OutEfePrdHou_DA'
                                                ,   '[AGUA]POCO[0].OutEfePrdMin_DA'
                                                ,   '[AGUA]LT_MAX_DA[0]'
                                                ,   '[AGUA]LT_MIN_DA[0]'
                                            )
                ORDER BY    ftd.DateAndTime
                FOR XML PATH('')
            )

I used the ";" tab but you can build the text however you want.

Browser other questions tagged sql sql-server

You are not signed in. Login or sign up in order to post.