STUFF ?

11 views
Skip to first unread message

Paul

unread,
Aug 12, 2021, 3:00:25 PMAug 12
to sequel-talk
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

Jeremy Evans

unread,
Aug 12, 2021, 4:01:32 PMAug 12
to seque...@googlegroups.com
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
Reply all
Reply to author
Forward
0 new messages