Need T-sql Help For Converting Rows From Multiple Tables Into Columns With Values In Sql Server 2005

I have a table tblUser: tblUser and a table tblBCE and data in the given below: tblBCE and a master table mstBCE which contains label text to display in front end application. Th

Solution 1:

My first thought is improve the schema and do you really need to do this.

To simplify the question it looks like you want to set the column name based on a join to mstBCE. You don't need a relation because the number of columns in tblBCE is fixed. Instead use dynamic sql to set the column names selecting from mstBCE pivoted onto one row.

DECLARE @sql nvarchar(4000); 
SELECT @sql = N'SELECT u.[username], u.[department], 
b.[Option1TB] as [' + pvt.[1] + N'], b.[Option1], 
b.[Option2TB] as [' + pvt.[2] + N'], b.[Option2], 
b.[Option3TB] as [' + pvt.[3] + N'], b.[Option3] FROM tblBCE as b 
JOIN tblUser as u ON b.[UserID] = u.[userid]; ' FROM (
    SELECT [tabconfigid], [tabdata] 
    FROM mstBCE 
    WHERE [tabType] = N'BCE'
) as m 
PIVOT ( MIN(m.[tabdata]) FOR m.[tabconfigid] IN ([1], [2], [3]) ) as pvt; 

EXEC (@sql);