create a field based on row values (pivot?)

Question

I have a query that returns multiple rows for results. My query is as follows:

select * from containertbl where bol_key = 236377502

My results look like this:

I would like for the results to look like this

I have tried pivot table (and made several attempts, but never could get any results) but I don't want the value to be the field name. Additionally, the number of containers is unknown.

Thank you in advance for any assistance!

Answer 1

I think dynamic sql is the only way to go in this case, since the exact number of pivoted columns is unknown. You might as well have a look here:

DECLARE @query AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

-- A nested query is used here to generate the required sequence for the column names of the
-- pivoted columns, i.e. [container1],[container2],[container3],[container4],[container5],[container6] etc. 
SELECT @ColumnName= ISNULL(@ColumnName + ',','') + QUOTENAME(PivotColumnName)
FROM (SELECT bol_key, containerId, 'container' + CONVERT(VARCHAR(10), rn) AS PivotColumnName
FROM (
   SELECT bol_key, containerId, ROW_NUMBER() OVER (PARTITION BY bol_key ORDER BY containerId) AS rn 
   FROM containertbl
) t ) s

--Prepare the PIVOT query using the columns names calculated
SET @query = 
  N'SELECT bol_key, ' + @ColumnName + '
    FROM (SELECT bol_key, containerId,' + CHAR(39) + 'container' + CHAR(39) + ' + CONVERT(VARCHAR(10), rn) AS PivotColumnName
          FROM (
            SELECT bol_key, containerId, ROW_NUMBER() OVER (PARTITION BY bol_key ORDER BY containerId) AS rn 
            FROM containertbl) t ) s    
    PIVOT(MAX(containerId) 
          FOR PivotColumnName IN (' + @ColumnName + ')) AS PvtTable'

--Execute the dynamic pivot query
EXEC sp_executesql @query

SQL Fiddle here