Advanced CrossTab/Pivot Query SQL Server

I have an extensive product database in SQL Server. I need to take 3 tables and combine them so that I have at the end a table with columns named after items of one table, with productid as rows with the field data concatenated data from 2 other tables. No mathematical functions are performed at all. Data is just collated. 1) Table 1 pdt_WebWidgets has 132 items I call "Widgets." Column names for the pivot crosstab will come from field labeltext. New widgets are occasionally added, so pivot must be able to include these new widgets as they are created. 2) Table 2 pdt_WebWidgetValue has over 7 million records, with fields ProductID, WidgetID, Value. pdt_WebWidgetValue.WidgetID=pdt_WebWidgetID 3) For [login to view URL] IN (2,6), we can join pdt_WebWidgetListBox (57,000 records) to pdt_WebwidgetValue ON pdt_WebWidgetListBox.ID=[login to view URL] AND (important) pdt_Webwidgetlistbox.WidgetID=[login to view URL] Again, this is only for items in Type=(2,6). For this WidgetType, we will concatenate the values in [login to view URL] as a comma-delimited list in the pivot fields. All other Types, we will just take the actual pdt_WebWidgetValue.Value. These should be single values, typically. I already have a query that does this, but it can be very slow. I use a number of static temp tables to help build the data to get it ready and make it easier/faster for the crosstab to be built. I wanted to see if someone could design something that would run much faster, creating a static pivot table after collating the data, so I could reduce a number of steps in a SQL Server Agent Job.