I received following question in email :
How to create a comma delimited list using SELECT clause from table column?
Answer is to run following script.
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+',' ,'') + Name
FROM Production.Product
SELECT @listStr
GO
I have previously written similar article where I have demonstrated this method using three sample examples. You can read the article for further information. SQL SERVER – Creating Comma Separate Values List from Table – UDF – SP
Reference : Pinal Dave (http://blog.SQLAuthority.com)
Hi Pinal ,
There is no need of COALESCE function for this string column with comma sepration. you can do it in this way also without using COALESCE. I did this thing in sql server 2000 and 2005
USE PUBS
GO
DECLARE @Result AS VARCHAR(2000)
SET @Results = ”
SELECT @Results = au_lname +’, ‘+ @Results FROM
authors
SELECT @Results
GO
—————————-And Your Query would be like this—–
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SET @listStr = ”
SELECT @listStr = Name + ‘, ‘ + @listStr
FROM Production.Product
SELECT @listStr
GO
Regards
Shashi Kant chauhan
Shashi Kant chauhan,
COALESCE is required for non null values. If there is NULL Value in your string you will have your resultset as NULL.
Using COALESCE is good idea.
Regards,
Pinal Dave
( http://www.SQLAuthority.com )
HI
i want to separate the values given in a single row separeted by comma into multiple row..
for eg
Input : ab,bc,cd,de
output:
ab
bc
cd
de
hi
did u get this query.
hi shyamala…in data step we can do this via…
data shyamu ;
infile ‘path of file’ dlm=’,’ ;
input variable @@;
run;
regards ;
i hope this works…
Hai,
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ‘‘’.
This error message is displayed when executing this query.
Hello, Pinal
I agree with you, COALESCE is a good idea (and a good function too), but in your script just the started value of the variable prevent a resultset NULL, but if any value from the list is null (in this case the “name” field), the entire resultset become NULL too. I suggest the follow solution:
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT
@listStr =
COALESCE ( COALESCE(@listStr+‘,’ ,”) + Name , @listStr)
FROM Production.Product
SELECT @listStr
GO
I thought these were all good suggestions until I realized it ws only good for one column.
Is there something similar that would do it for all coluns in a table. For example, I create a temp table with all the data I need to Export then I do something like this:
Select
Column_1+’,'+
Column_2+’,'+
Column_3+’,'+
Column_4+’,'+
Column_5+’,'+
Column_6+’,'+
Column_7+’,'+
Column_8
From TableToExport
Any suggestions? Some times I may have 100 or more columsn from various tables.
Thanks
Hi ,
My name is Sushma.
I am new to learning SQL SQRVER (developer).
I need some programs links.
I am new to this so i am faceing lot of problems(even i dont know codding) plz help me.
Thanks
Sushma.
–create or replace function getAuthor(i_deptNo VARCHAR)
– return VARCHAR(4000)
– is
– cursor c_emp is
– select first_Name from employee where id = i_deptNo;
– v_out VARCHAR2(4000);
– begin
– for r_emp in c_emp loop
– if v_out is null then
– v_out:=r_emp.first_Name;
– else
– v_out:=v_out||’, ‘||r_emp.first_Name;
– end if;
– end loop;
– return v_out;
– end
Try it !!!
Kishor singh
I need learnig the QSL-Server also
Need quik help now!
Get job in 3 dayz
You give help me?
Henk, did you get the job? How did you manage with SQL?
Hi,
Can i use comma seperation in Where clause?
Such as where age=40,height=5; etc?
Thanks
Om
I was looking for a way to convert a column into a comma delimited list, and this worked great, thanks!
This is really great. Thank you.
Is it possible to improve it to include a GROUP BY? I have a table with two columns (ID, Marker). I want to list all of the Markers that exist for each ID as in:
ID Marker_List
————————————–
1 001, 002
300 002
478 008, 985, 212
Kevin,
In GROUP BY any COLUMN reported must be also in the GROUP BY, hence there is nothing to aggregate.
hi
i have a requirement
*12345678901115100609*22345678901115100609*12355678901115100609*’
this is string in sqlserver..i want first 10 digits as machine code
next 4digits as a time and last 6digits as date..”*” symbol is
neglected
result like:
machine time date
1234567890 1115 100609
2234567890 1115 100609
In the above example i want to pass the table name as parameter.
can anybody help me to do this.
thanks
Nagendra
Hi Freinds,
I have one Query is,
I have one Table. It has a colum Named RoleID. in this column i have records like ’3,4,6′. and my query is if i want to select value for ’4′.
For Example: In Table I have 5 records. in RoleID column like this
ID Name RoldeID
——————————-
1 Mr.A 2
2 Mr.B 2,5
3 Mr.C 3,4
4 Mr.D 2,3,5
5 Mr.E 3,5
Now I want to select Records where RoleId = 3.
when i pass select query like this,
Select * from table where RoleID = 3. then it is not getting any data.
I m New in SQL please Help.
Thanks,
Mazhar
Got solution? I am also having same problem…
Since the column datatype is a VARCHAR type and you have combination of values, please use like operator.
Select * from table where RoleID like ‘%3%’
I think this will not work accurately. This will return the records e.g. roleid 33 ,333,113 and so on
Hi to All,
if you add an ORDER BY clause, and if it’s order by a varchar column there is a problem. Example:
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name
FROM Production.Product
ORDER BY COLUMN_NAME
SELECT @listStr
GO
If a COLUMN_NAME is a VARCHAR type, result of a query is not comma separated list but a single item. In your example, it would be a single name without a comma. If a COLUMN_NAME is a date or int type, it works just fine. What’s the catch?
This works great
What if you have a (,) within a string, and you just want
to separate the each side into it’s own column.
Example:
12345678,abcdefgh
How can this be done?
Hi Vikram,
You can do this as:
DECLARE @str VARCHAR(100)
SELECT @str = ’12345678,abcdefgh’
SELECT @str,
SUBSTRING(@str, 0 , CHARINDEX(‘,’,@str)) AS [1stColunmn],
SUBSTRING(@str, CHARINDEX(‘,’,@str) + 1, LEN(@str)) AS [2ndColumn]
Thanks,
Tejas
hai
Could any one help me how to get rid of my problem
My problem is:
table name: countryID
accid localaccid names
———————————————–
101 222 india
102 333 india
103 444 india
201 777 usa
202 888 usa
————————————————
I need like this
names localaccid accid
————————————————–
india 222,333,444 101,102,103
usa 777,888 201,202
—————————————————
please how to solve this problem.
hai
Could any one help me how to get rid of my problem
My problem is:
table name: countryID
accid localaccid names
———————————————–
101 222 india
102 333 india
103 444 india
201 777 usa
202 888 usa
————————————————
I need like this
names localaccid accid
————————————————–
india 222,333,444 101,102,103
usa 777,888 201,202
—————————————————
please how to solve this problem.
It’s easy way.. just follow my instruction.
Step 1:
Create Function..
CREATE FUNCTION [dbo].[udf_UserLocation]
(@id int)
RETURNS VARCHAR(500)
AS
BEGIN
declare @loctemp varchar(500)
SELECT @loctemp=COALESCE(@loctemp + ‘, ‘, ”) + CAST(l.LocName as varchar)
FROM Location where CountryID=@id
RETURN (@loctemp)
END
Then
Run Query..
select CountryName,dbo.udf_UserLocation(id) as ‘Location’ from [Country]
You will get output as u want without using cursor.
–dharmik chotaliya
Hi Pinal,
I want this in Select distinct query result.
It is not working in case of distinct
I have tried like following and i didn’t get desired result
USE AdventureWorks
GO
DECLARE @listStr VARCHAR(MAX)
SELECT DISTINCT @listStr = COALESCE(@listStr+’,’ ,”) + Name
FROM Production.Product
SELECT @listStr
GO
Plz give some solution
please try below query
use AdventureWorks
Go
Delcare @listStr VARCHAR(MAX)
select @listStr =COALESCE(@listStr+’,’ ,”) + ProductData.Name
from(select distinct Name from Production.Product) as ProductData
SELECT @listStr
Go
Hi Pinal,
I got the solution for Select Distinct
but i don’t know about SQL Server 2005 but it is working in SQL Server 2008
USE AdventureWorks
GO
DECLARE @listStr XML
set @listStr=(SELECT DISTINCT COALESCE( Name,”)+’,’
FROM Production.Product
for XML Path(”))
SELECT SUBSTRING(CAST (@ listStr AS VARCHAR) , 1, LEN(CAST (@ listStr AS VARCHAR))-1)
GO
I have two tables (please note that column headers are dynamic – not necessarily 3 or 4 columns..but the same numbr of records will be present in the column data):
Table 1:
SortSeqID ColumnHeader
1 ‘ColA,ColB,ColC’
2 ‘ColA,ColB,ColC,ColD’
3 ‘ColA,ColC,ColD’
Table 2:
SortSeqID ColumnData
1 ‘1,2,3’
2 ’11,13,243,5’
3 ‘1,5,6’
Need output like:
SortSeqID ColA ColB ColC ColD
1 1 2 3
2 11 13 243 5
3 1 5 6
Refer this
http://beyondrelational.com/blogs/madhivanan/archive/2008/09/11/splitting-delimited-data-to-columns-set-based-approach.aspx
Hi Pinal,
Can I use COALESCE in update statement ?
when I am trying to update the rows to temp table the concatenation doesn’t happen.
It picks only the first value
Please help
Create Table #TempData
(
Pe_Logn Varchar(10), Team Varchar(8000)
)
Insert Into #TempData
Select Distinct(PE_DEPT), NULL from iP_Person
/* Select *from #TempDate */
Pe_Logn Team
Finance NULL
HR NULL
IT NULL
/* Getting the people for each department */
Update #TempData
Set Team = COALESCE(IsNull(TEAM, ”) + ‘,’,”) + Pe.Pe_Logn from iP_Person Pe
Where Pe.Pe_Dept = #TempData.Pe_Logn
/* Select *from #TempData */
Pe_Logn Team
Finance ,ABC
HR ,DEF
IT ,XYZ
Actual output should have been
Pe_Logn Team
Finance ,ABC, 123, !@#
HR ,DEF, 234, @#$
IT ,XYZ, 345, #$%
Regards,
Uday
Hi!
I have a query like this:
InvoiceNum Depart.
————— ————–
11000 400
11000 404
15000 572
And I need:
InvoiceNum Depart.
————— ————–
11000 400, 404
15000 572
Is it possible? Thanks!!!!
Search for rowset concatenation in this site
If I want this view would COALESCE work? How else can I get this please?
Data – All in one table
Col1 Col2
—— ———-
USA Denver
USA Seattle
USA Chicago
Canada Vancouver
Canada Calgary
Result –
Col1 Col2
—– ———–
USA Denver, Seattle, Chicago
Canada Vancouver, Calgary
Pinal,
How can I use this comma separated list in a query? like where value IN ( Select commaSeparatedList from MyTable)
This script doesn’t work if there are any null values in the set of rows being composited.
Try this instead:
DECLARE @List VARCHAR(5000)
SELECT @List = COALESCE(@List + ‘, ‘ + MyField, MyField)
FROM MyTable
select @List as MyDisplayName
(from http://www.mitchelsellers.com/blogs/articletype/articleview/articleid/289/creating-comma-separated-list-in-sql.aspx)
Only slightly different but more robust
Hi Pinal,
I have taken one variable named @Period_List As varchar(max). I have set its vale as
@Period_List = ‘column1 int, column2 int, column 3′
Now i am trying to create table using this variable,
Like,
CREATE Table #TempTable (@Period_List)
But it gives error. can you please tell me another way or solution for this problem.
I want help with sql statment. when I select the data I get is like this
1 2232 eq233 1000 12-12-2011 11:11 39 9.2 text text
I want this output to have comma instead of space like
1, 2232, eq233, 1000, 12-12-2011 11:11, 39, 9.2, text text,
can some please help.
In the toolbar Goto Query–>Query options–>Results–Text . Select output format as comma delimited
Now set the result mode to text and run the query
Can someone please help me? I have a column of string data collected from a survey. e.g. (Yes; No; Maybe; Don’t know), assigned to a number of respondents. I want to create a list in a seperate COLUMN (retaining a Response heading) for each respondent. i.e.
Respondent Response
A Yes
A No
A Yes
B Maybe
B No
B Yes
Hi pinaldave,
The code is help me a lot in one of my project.
I really appreciate.
Thanks & Regards,
Prashant Kauthekar
Hi Pinal Dave,
I have referenced this code many times. I appreciate you maintaining this site. Nicely done!
-Steve
hi
i want to separate the values given in a single row separeted by comma into multiple row..
for eg
Input : ab,bc,cd,de
output:
ab
bc
cd
de
@Mazharkhan
Did you find your answer..i have same problem..please revert
HOW TO USE SELECT STATEMENTS INSTEAD OF ARGUMENTS IN SPLIT COMMA FUNCTION incase of ms sql 2008 server?it returns value for the below query
select LISTMEMBER from SysAdmin.fnSplitComma (’2,3,5′)
like LISTMEMBER
2
3
5
but how to get the value when we specifies a column in table which has data in the format 2,3,4 in first row,2,5 in second row.BELOW select statement wont work for that.
SELECT LISTMEMBER FROM SysAdmin.fnSplitComma(SELECT IMPLanguages FROM CFI.InstructionalMediaPackage WHERE IMPLanguages=p.pkLanguageID) AS
‘nameList’
FROM SysAdmin.M_Lang AS p
perfect!
I have a table with FirstName & Last Name as 2 fields, Help me to get all the full name i.e First Name & Last name’s in a single string.
table1
——
FName LName
—— ——–
S Thakur
P Patel
R Singh
Result should be : S Thakur, P Patel, R Singh
Please help me in this
Hi Priyanka
Try something like this (it’s very similar to the code in the original code post)
declare @FullName varchar(Max)
select
@FullName = coalesce(@FullName + ‘, ‘,”) + FName + ‘ ‘ + LName
from table1
The only difference is you are now coalescing on a concatenated string of the FName and LName
Hope this helps
I tried using COALESCE on SQL Server 2008, but it was giving very inconsistent results. When running as a query, the list would look fine, but if I put the same code into a trigger, there would always be a comma prepended to the list.
I ended up replacing the COALESCE with an equivalent CASE statement which solved the issue:
@S = (CASE WHEN @S IS NULL THEN ” ELSE @S + ‘,’ END) + @ColValue
Hi Pinal…
your articles are very useful…I am an sql server database beginner.
I read that “we can change environment variables using sp’s ,but can’t with udf’s ” from one of your article -stored procedure vs udf.
My doubt is:
How to set environment variables using stored procedures.i did not know how to get those env var values ti t-sql.So,please give me an example to do this.It may be useful for beginners like me..
Thanks,
durga
Hi Pinal…
your articles are very useful…I am an sql server database beginner.
I read that “we can change environment variables using sp’s ,but can’t with udf’s ” from one of your article -stored procedure vs udf.
My doubt is:
How to set environment variables using stored procedures.i did not know how to get those env var values ti t-sql.So,please give me an example to do this.It may be useful for beginners like me..
Thanks,
durga
I am having same problem which Steve has mentioned above. Is anyone know the solution of this problem ?
Hi Pinal,
I have a problem trying to resolve this since two days but no use , could you please help me on this
Source table
Id Product
111 BCM123
111 BCM456
111 BCM789
222 BCM123
222 BCM456
222 BCM789
I am looking for the output like this
Id Product
111 BCM123,BCM456,BCM789
222 BCM123,BCM456,BCM789
Thanks in advance
Hi all,
Wanted to know if we can get comma separated list for 1 column and all get rest columns w/o commas in a query having multiple joins?
Thanks,
hello Pinal,
I have a requirement where a variable with comma separated value is passed as a parameter to a SP and those comma separated values are the field names.Here I want to display only the columns mentioned in the input paramenter:
Ex:-
Table1 is a table with following columns
Col1
Col2
Col3
Col4
Col5
Col6
Col7
Col8
@Param=’Col1,Col2,Col3,Col5,Col8′ — this is the parameter that wl be input to SP.
and I want the above mentioned columns to be displayed when the SP is executed.
Thanks in Advance
[...] SQL SERVER – Create a Comma Delimited List Using SELECT Clause From Table Column [...]
how can i display idcount in message box ? i am using Linq to sql and this is my stored procedure.. please replyy……
ALTER PROCEDURE [dbo].[Transaction]
(
@Patient nvarchar(50),
@E_TO nvarchar(50),
@R_type int,
@User_id uniqueidentifier,
@ReportType nvarchar(50),
@Patient_no int,
@Patient_ID_NO numeric(10,0),
@idcount numeric(18,0)=NULL output
)
AS
BEGIN
declare @tempid numeric(18,0)
set @tempid = 0;
declare @idcnt numeric(18,0)
select @idcnt =isnull( max(idcount),0) from Transactions where year(R_date)=year(getdate())
if (@idcnt =0)
set @tempid=1
else
set @tempid = @idcnt +1
INSERT INTO dbo.Transactions
(
Patient,E_TO,R_date,R_from,[User_id],
report_type,Patient_no,Patient_ID_NO,idcount
)
values
(
@Patient,@E_TO,getdate(),@R_type,@User_id,@ReportType,@Patient_no,@Patient_ID_NO,@tempid
)
End
Any Suggestions for how I could do this grouping by a header ID. Example: i have 1 invoice with 5 services, I want 1 row with 2 columns, Invoice and list of services. Here is how i am currently pulling this off using a subquery on the table i am selecting from, but i want to make it perform better:
Stuff(
(select ‘, ‘ + ServiceTypeWithNotes
From WisCTE c
where c.WorkItemID = cte.WorkItemID
FOR XML Path(”)
),1,1,”) as ServiceList
SELECT *FROM A
SELECT *FROM C
SELECT *FROM A
WHERE NO1 NOT IN
(
SELECT NO1 FROM C
)
CREATE FUNCTION ITSV2(@A VARCHAR(10))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @B VARCHAR(10)
SET @B=”
SELECT @B=NO1+’,'+@B FROM A
WHERE NO1 NOT IN
(SELECT
NO1
FROM
C
)
RETURN SUBSTRING(@B
END
SELECT dbo.ITSV2(”)
That only returns one column. I am looking for 2, first column being the header rowID the second being list of services for header rowID.
I have a table with
Header rowID Service
1. A
1. B
1. C
I’m looking for
1. A,B,C
With out doing a sub query joing on itself
Select
RowID
,(
Select stuff(CommaDelimintedserviceList)
From table1 b
Where b.rowID = a.rowID
)
From table1 as a
Or is a best way?
Yes thats a good way Otherwise do it inf the front end application
NICE EX.
This is awesome along with everything else from Pinal Dave.
Thanks
Ned
hi pinal;
i want to store the select clause values into string.if i use one column able to store into string,but more than one column not able to store,please give any suggestion.
If you want to store more than one column values, how do you want to be added a comma seperated?
I want to pass my result of in my select query ?? like this
DECLARE @MaxTRxID as int
DECLARE @Str as varchar(MAX)
DECLARE @Str1 varchar(MAX)
SET @MaxTRxID = 1700
SELECT @Str = COALESCE(@Str + ‘,’, ”) + ReferenceNo FROM [iC.SI.WC.PosLog.TrxInfo]
WHERE TrxId > @MaxTRxID
SET @Str1 = @Str
select * from [iC.SI.WC.PosLog.CashPayment.LineItem] where ReferenceNo in (@Str1)
but its not working can anyone help?
pLEASE SOMEONE HELP ME
Hello Guys,
I would appreciate if anyone could help me.
I am using COALESCE exactly like in the initial example of the post (copied below)
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + Name
FROM Production.Product
SELECT @listStr
My list is also Varchar(max) but i want to retrieve these values in ascending format using order by?
Can i do this? and if yes could you please give me a simple example using the above script?
Also if i cannot do this do you have any other idea how to achieve the same results?
Thanks in advance!!
This resolved with table variable in a function
DECLARE @TableVar table (list VARCHAR(max) NOT NULL)
INSERT INTO @TableVar ( list )
SELECT Name
FROM Production.Product
ORDER BY Name
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr + ‘ ; ‘ , ”) + list
from @TableVar
RETURN @listStr
it is possible to insert this code to function, which I able to call in the application code
Now I have somone likie this:
create procedure LIST_p(@COLUMN varchar (max), @TABLE varchar (max) )
AS
DECLARE @SYNTAX VARCHAR(MAX)
SET @SYNTAX = ‘DECLARE @listStr VARCHAR(MAX) select @listStr = COALESCE(@listStr+”,” ,””) + ‘ + @COLUMN + ‘ from ‘+ @TABLE +’; select @listStr;’
execute (@SYNTAX)
go
, but I have a question how to execute this procedure using select query
Hi Pinal,
How to create comma separated list of values by group.
Hi this is satish bandi
I want only in singe row from two rows Like
Sno Name Age
”””””|”””””””’|””””’
1 sa 20
1 sa 30
1 sa 40
Now I want
Sno Name Age
”””””|”””””””’|””””’
1 sa 20,30,40
Hi This IS satish
id name services(I have)
———————————–
1 Joe AA
1 Joe AB
1 Joe AC
2 Judy GH
2 Judy GC
3 Kevin AA
3 Kevin GH
Result Set:
id name services (I want)
———————————–
1 Joe AA, AB, AC
2 Judy GH, GC
3 Kevin AA, GH
How can i get like this
solution to vyshu’s problem
declare @tt varchar(25) = ”
,@ttlen tinyint = 0
select @tt = ‘ab,bc,cd,de’
,@ttlen = LEN(@tt)
create table #tt(name varchar(10))
while(@ttlen > 0)
begin
insert into #tt
select LEFT(ltrim(@tt),2)
select @tt = STUFF(@tt,1,3,”)
set @ttlen = LEN(ltrim(@tt))
end
select * from #tt
same output i wanted using user defined function , i wanted to pass the ids with comma separed to the function and get the description as per the id.
ex: my passing dynamic parameters ids should be like this ids = ’1,4,5,6′ and from function it should return with comma separated description..like san,ta,sri,ssss.
Thanks
Santosh
Hi Pinal,
there is a task in which user is asking for comma delimited text file. i am running the query in sql server 2008.
Hello Pinal,
I have a problem I have been trying to solve. I have an EDI 834 text file I need to parse into a SQL table using multiple custom delimiters. The file is delimited with a { and member data between different leading identifiers, for example ~DTP{348{D8{20121101~AMT{C1{0 would mean for the month of 2012 10 01 the copay would be $0. The other leading indentifiers would be ~INS – Insured Benefit OR MEMBER LEVEL DETAIL
~REF – Reference Information : The REF segment is required to link the dependent(s) to the subscriber.PRIOR COVERAGE MONTHS
~DTP – Date or Time or Period
~NM1 – Individual or Organizational Name : Either NM1 or N1 will be included depending on whether an individual or organization is being specified. MEMBER EMPLOYER or RESPONSIBLE PERSON
~PER – Administrative Communications Contact
~N3 – Party Location
~N4 – Geographic Location
~DMG – Demographic Information
~LS – Loop Header
EC – Employment Class
~LS – Loop Header
~LX – Transaction Set Line Number
~N1 – Party Identification
~REF – Reference Information
~AMT – Monetary Amount Information
~COB – COORDINATION OF BENEFITS
~LX – Transaction Set Line Number
~N1 – Party Identification
~DTP – Date or Time or Period
~LE – Loop Trailer
~SE – Transaction Set Trailer
Any help of direction would great.
thanks
DECLARE @listStr VARCHAR(MAX)
SELECT @listStr = COALESCE(@listStr+’,’ ,”) + COALESCE(Name,”)
FROM Production.Product
SELECT @listStr
COALESCE would be required for “Name” too
How could you create a set of inline comma-separated values for a query? For example, I tried to show orders and include the orderTypes from a cross-reference table:
SELECT OrderId, Name,
(
SELECT COALESCE(Name+’, ‘ ,”) + Name
FROM OrderType JOIN OrderType_XR ON OrderType_XR.OrderTypeId = OrderType.OrderTypeId WHERE OrderType_XR.OrderId=OrderId
)
FROM Order
but SQL Server reports “Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, , >= or when the subquery is used as an expression.” Can this be done inline or would I need a SQL function?