On Thu, Aug 12, 2021 at 12:00 PM Paul <[email protected]> wrote:
> Anyone wanna take a stab at how you'd write this in sequel (MSSQL) :
>
> select MessageID, STUFF(
> (
> SELECT DISTINCT(', ' + t2.PatientName)
> FROM XDS_DOCUMENTS t2
> WHERE t1.MessageID=t2.MessageID
> and t2.PatientName is not null
> FOR XML PATH(''), TYPE
> ).value('.','varchar(max)')
> ,1,2, ''
> ) AS PTNAME
>
> from XDS_DOCUMENTS t1 group by MessageID
>
Sequel currently does not have direct support for the Microsoft SQL Server
SELECT FOR clause. So the best you could currently do is:
DB[:XDS_DOCUMENTS].
select_group(:MessageID).
select_append{STUFF(Sequel.lit(<<SQL), 1, 2, '').as(:PTNAME)}
(SELECT DISTINCT(', ' + t2.PatientName)
FROM XDS_DOCUMENTS t2
WHERE t1.MessageID=t2.MessageID
and t2.PatientName is not null
FOR XML PATH(''), TYPE
).value('.','varchar(max)')
SQL
It is possible to add support for the SELECT FOR clause to Sequel, but from
my brief research, it's specific to MSSQL, and doesn't appear to be a
common need. So it's not something I have current plans to work on.
Thanks,
Jeremy
--
You received this message because you are subscribed to the Google Groups
"sequel-talk" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sequel-talk/CADGZSSf%2BG1NxonY80sYtEBtH5kghSEC7Mfq0jwG37rTziqVmdA%40mail.gmail.com.
