0

I have a Jenkins batch command step, which makes query to MSSQL DB and return a result:

cd "C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\110\Tools\Binn"

SQLQUERY="C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.exe" -S "test-sqlsrv.database.windows.net" -U "someuser" -P "somepass" -d "somedb" -W -Q "SELECT tt.Name FROM Transport.Topic tt, Transport.Root tr, Transport.DDS dds WHERE tr.Id = tt.TransportId AND dds.TransportId = tr.Id AND dds.QOSProfile = 'Asset_Profile' group by tt.Name order by tt.Name">test.txt

Result in test.txt:

Name
----
value1
DigitalIO
value2
J1939
SKF15
value3
VIMS

(7 rows affected)

Is there any way, either by transform test.txt result, or by remaking sql query on previous step, to receive following output in file or variable:

    value1;DigitalIO;value2;J1939;SKF15;value3;VIMS
Vasyl Stepulo
  • 1,493
  • 1
  • 23
  • 43
  • 1
    try this query `with t as ( select 'value1' as Name union all select 'DigitalIO' union all select 'value2' union all select 'J1939' union all select 'SKF15' union all select 'value3' union all select 'VIMS' ) select distinct stuff(( select ',' + Name from t for xml path ('') ),1,1,'') from t` – James Jun 14 '18 at 12:48
  • @JaimeDrq sorry, I'm newbie in sql queries - can you clarify - I need to run this as a part of query mentioned in me question? Or as a second query? because it gives me a syntax error. – Vasyl Stepulo Jun 14 '18 at 13:03
  • Try to run this query isolated, not inside a batch of queries, because the with clause must be always preempted with a semi-colon. I cannot ask this question because it has been already marked as duplicated. – James Jun 14 '18 at 13:04
  • You can try it works from here: https://data.stackexchange.com/stackoverflow/query/862683/50857777 – James Jun 14 '18 at 13:09
  • Ok, this query gives me my expected result, as I wanted, but how this implement? Because outputs are different on different db's? – Vasyl Stepulo Jun 14 '18 at 13:09
  • This is sql server sintax, and you marked that technology in your description – James Jun 14 '18 at 13:10
  • Oh, thanks for link you provided. I will try to assemble work solution. – Vasyl Stepulo Jun 14 '18 at 13:10
  • ok good luck! :) – James Jun 14 '18 at 13:14
  • Thanks, I was able to assemble right query, using your approach! You can copy it as an answer, so I can mark it as a right comment. – Vasyl Stepulo Jun 14 '18 at 13:37
  • No problem don't worry, I can't because it has been marked as duplicated. Happy to know it works – James Jun 14 '18 at 13:38
  • @JaimeDrq Anyway, thanks one more time :) – Vasyl Stepulo Jun 14 '18 at 13:39

0 Answers0