alternative to CROSS APPLY
<p>I have a stored procedure that I use to squeeze multiple rows of data into one variable, like so:</p>

<pre><code>DECLARE @Description VARCHAR(200);

SELECT @Description = COALESCE(@Description) + ', ', '') + [Description]
FROM dbo.DrugDescriptionAndImprints
WHERE NDC = @NDC;
</code></pre>

<p>My problem is, I recently had a need to accomplish this for multiple NDCs simultaneously for generating report data.</p>

<p>My current solution is to use <code>CROSS APPLY</code> with a table-valued function, but that is less than optimal due to the performance hit. I would like to be able to "flatten" this data in a view or something, but am unable to use variables in a view. Is there a way to accomplish this without using <code>CROSS APPLY</code> and table-valued functions?</p>

<p>Here is the table-valued function for reference:</p>

<pre><code>CREATE FUNCTION [dbo].[fnFlatDescriptionByNdc]
(
    @NDC VARCHAR(11)
)
RETURNS 
@ret TABLE 
(
    [Description] VARCHAR(200)
)
AS
BEGIN
    DECLARE @Description VARCHAR(200)
    DECLARE @Imprint1 VARCHAR(40)
    DECLARE @Imprint2 VARCHAR(40)

    SELECT @Description = Coalesce(@Description + ', ', '') + [Description]
    FROM dbo.DrugDescriptionAndImprints WHERE NDC = @NDC

    SELECT @Imprint1 = COALESCE(Imprint1, '')
    FROM dbo.DrugDescriptionAndImprints WHERE NDC = @NDC 
    ORDER BY Imprint1 DESC

    SELECT @Imprint2 = COALESCE(Imprint2, '')
    FROM dbo.DrugDescriptionAndImprints WHERE NDC = @NDC 
    ORDER BY Imprint2 DESC

    INSERT INTO @ret ([Description])
    SELECT @Description + CASE WHEN @Imprint1 &lt;&gt; '' OR @Imprint2 &lt;&gt; '' THEN ' (' +
        CASE WHEN @Imprint1 &lt;&gt; '' THEN @Imprint1 + 
            CASE WHEN @Imprint2 &lt;&gt; '' THEN ' / ' + @Imprint2
            ELSE ''
            END
        ELSE @Imprint2 END + ')' ELSE '' END

    RETURN 
END
</code></pre>

<p>I am working with SQL Server 2008 R2.</p>

<p><strong>EDIT</strong>
Here is some sample data to show what I am working with:</p>

<pre><code>NDC              ProdDescAbbr           Description        Imprint1      Imprint2
00005250033      FIBERCON   TAB 625MG   film-coated        LL            F 1
00005250033      FIBERCON   TAB 625MG   tan                LL            F 1
00005250033      FIBERCON   TAB 625MG   scored             LL            F 1
00005250033      FIBERCON   TAB 625MG   oblong             LL            F 1
</code></pre>