I am interested as to how or if we can pull a cross-tab or pivot dynamically. I have a query that can have an undetermined amount of comments associated to one job. In the query I need to show these comments in one line.

SET NOCOUNT ON

CREATE TABLE #BidComments (BidId int, Comment VARCHAR(varchar(max))

INSERT INTO #BidComments (BidId , Comment)

SELECT 1, 'This is comment 1 for bid 1' UNION ALL

SELECT 1, 'This is comment 2 for bid 1' UNION ALL

SELECT 1, 'This is comment 3 for bid 1' UNION ALL

SELECT 2, 'This is comment 1 for bid 2' UNION ALL

SELECT 2, 'This is the second comment for bid 2' UNION ALL

SELECT 2, 'Here we have the third comment for bid2' UNION ALL

SELECT 2, 'Comment 4' UNION ALL

SELECT 3, '1 comment on 3' UNION ALL

SELECT 3, 'comments for 3, the second' UNION ALL

SELECT 3, 'comment 3' UNION ALL

SELECT 3, 'Comment 4' UNION ALL

SELECT 3, 'Comment 5' UNION ALL

In the end I need to display it as

BidID Comment1 Comment2 Comment3

Any thoughts?