Multiple rows in a Sql Server column

Question

Multiple rows in a Sql Server column

Navigation

#1 by (0 votes)

1

I have a problem where I need to merge the query return into two tables in a single row.

I thought about using PIVOT but could not because of INNER JOIN .

The query looks 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 for face value of IN .

I need all result to come in a single line, each interval will generate more than 400 data.

sql sql-server

asked by anonymous 04.07.2018 / 14:16

1 answer

Information is not loaded in MODAL Edit After Page Doubt about pycharm

score 0 · Accepted Answer

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 ";" but you can build the text as you see fit.