SQL CSV as Query Results Column

Question

I have the following SQL which queries a single table, single row, and returns the results as a comma separate string e.g.

Forms
1, 10, 4

SQL :

DECLARE @tmp varchar(250)
SET @tmp = ''
SELECT @tmp = @tmp + Form_Number + ', '
FROM  Facility_EI_Forms_Required
WHERE Facility_ID = 11 AND EI_Year=2012 -- single Facility, single year
SELECT SUBSTRING(@tmp, 1, LEN(@tmp) - 1) AS Forms

The Facility_EI_Forms_Required table has three records for Facility_ID = 11

Facility_ID EI_Year Form_Number
11  2012    1
11  2012    10
11  2012    4

Form_number is a varchar field.

And I have a Facility table with Facility_ID and Facility_Name++.

How do I create a query to query all Facilites for a given year and produce the CSV output field?

I have this so far:

DECLARE @tmp varchar(250)
SET @tmp = ''
SELECT TOP 100 A.Facility_ID, A.Facility_Name,
    (
    SELECT @tmp = @tmp + B.Form_Number + ', '
    FROM  B
    WHERE B.Facility_ID = A.Facility_ID 
    AND B.EI_Year=2012
    )
FROM Facility A, Facility_EI_Forms_Required B

But it gets syntax errors on using @tmp

My guess is this is too complex a task for a query and a stored procedure may be need, but I have little knowledge of SPs. Can this be done with a nested query?

I tried a Scalar Value Function

ALTER FUNCTION [dbo].[sp_func_EI_Form_List] 
(
    -- Add the parameters for the function here
    @p1 int,
    @pYr int
)
RETURNS varchar
AS
BEGIN
    -- Declare the return variable here
    DECLARE @Result varchar

    -- Add the T-SQL statements to compute the return value here
    DECLARE @tmp varchar(250)
    SET @tmp = ''
    SELECT @tmp = @tmp + Form_Number + ', '
    FROM OIS..Facility_EI_Forms_Required
    WHERE Facility_ID = @p1 AND EI_Year = @pYr -- single Facility, single year

    SELECT @Result = @tmp -- SUBSTRING(@tmp, 1, LEN(@tmp) - 1)-- @p1

    -- Return the result of the function
    RETURN @Result

END

The call

select Facility_ID, Facility.Facility_Name, 
   dbo.sp_func_EI_Form_List(Facility_ID,2012)
from facility where Facility_ID=11

returns

Facility_ID Facility_Name   Form_List
11  Hanson Aggregates   1

so it is only returning the first record instead of all three. What am I doing wrong?

score 1 · Accepted Answer · edited May 23 '17 at 10:29

Try the following approach, which is an analogy to SO answer Concatenate many rows into a single text string. I hope it is correct, as I cannot try it out without having the schema and some demo data (maybe you can add schema and data to your question):

Select distinct A.Facility_ID, A.Facility_Name, 
    substring(
        (
            Select ',' + B.Form_Number AS [text()]
            From Facility_EI_Forms_Required B
            Where B.Facility_ID = A.Facility_ID 
            AND B.EI_Year=2012
            ORDER BY B.Facility_ID
            For XML PATH ('')
        ), 2, 1000) [Form_List]
From Facility A

Worked great! `For XML...` is a bit of black magic IMO. Produces a result but no clue as to how. In the same class as regex I guess. — rheitzman, Jan 11 '17 at 20:41

SQL CSV as Query Results Column

1 Answers1