Displaying resultset rows as columns

Question

I have a SQL stored procedure that will return only one record. However, in that proc, is a subquery, that can return more then one rows. I believe it might be best design the sub query to return one row of multiple columns, instead of multiple rows.

How would I write out the sub query so it returns columns.
Currently the query is:

SET @Pkg_Status = (Select lf.name, edi.Date from EdiPackage edi, Labelfeed lf
     Where edi.orderID = @OrderID
     AND edi.code = lf.code
     AND lf.labelID = 'EDIStage')  

A sample resultset from the above query would be:

Column 1             Column 2  
Field1.Value1     Field2.Value1  
Field1.Value2     Field2.Value2  
Field1.Value3     Field2.Value3  

Instead, I would like the results to be:

Column 1      | Column2       | Column3       | Column4      |  Column 5       | Column 6  
Field1.Value1 | Field2.Value1 | Field1.Value2 | Field2.Value2 | Field1.Value3 | Field2.Value3

How can this be done?

Answer 1

First, you should use the ISO Join keyword when joining two tables instead of separating the tables via comma and then "joining" them in the Where clause. So our query would be:

Select LF.name, EDI.Date 
From EdiPackage As EDI
    Join LabelFeed As LF
        On LF.Code = EDI.Code
Where EDI.orderID = @OrderID
    And LF.labelID = 'EDIStage'

Second, your original example is setting the results of a query to a variable. That will not work with multiple columns or rows. At best, SQL might simply take the first column in your subquery. Lastly, what it sounds like you seek is something of a crosstab. We would need to see the data, but you can achieve what you want by doing something like so:

Select Min( Case When LF.Name = 'Value1' Then LF.Name End ) As Col1
    , Min( Case When LF.Name = 'Value1' Then EDI.Date End ) As Col2
    , Min( Case When LF.Name = 'Value2' Then LF.Name End ) As Col3
    , Min( Case When LF.Name = 'Value2' Then EDI.Date End ) As Col4
    , Min( Case When LF.Name = 'Value3' Then LF.Name End ) As Col5
    , Min( Case When LF.Name = 'Value3' Then EDI.Date End ) As Col6
From EdiPackage As EDI
    Join LabelFeed As LF
        On LF.Code = EDI.Code
Where EDI.orderID = @OrderID
    And LF.labelID = 'EDIStage'

In this case, you would replace 'Value1', 'Value2' and 'Value3' with the data values that should be used to differentiate one column from another.

Answer 2

You should write a stored procedure that will create a new memory table that you can dynamically add columns to and each column will have a different row.

Answer 3

In SQL 2005 and up, you would use pivot.

If the number of columns is fixed, use the answer Thomas gave.

Else, to squish them into one field, you would do something like

Declare @Pkg_Status Character Varying(8000)
Select @Pkg_Status = 
  Coalesce(@Pkg_Status + ',' + LF.Name + ',' + Cast(EDI.Date As Character Varying),
           LF.Name + ',' + Cast(EDI.Date As Character Varying))
From
  dbo.EDIPackage As EDI
  Inner Join dbo.LabelFeed As LF
    On EDI.Code = LF.Code
Where
  (EDI.OrderID = @OrderID)
  And (LF.LabelID = 'EDIStage')