需要T-SQL帮助才能将多个表中的行转换为Sql Server 2005中具有值的列 [英] Need T-SQL help for converting Rows from multiple tables into Columns with values in Sql Server 2005

问题描述

我有一个表tblUser:

I have a table tblUser:

tblUser

以及下表tblBCE和数据:

and a table tblBCE and data in the given below:

tblBCE

和一个主表mstBCE，其中包含要在前端应用程序中显示的标签文本.该表还包含其他选项卡的数据，但是目前我只想要tabType'BCE'，一旦有了这个概念，我将自己处理其他选项卡.

and a master table mstBCE which contains label text to display in front end application. This table contains data for other tab also but currently i want only for tabType 'BCE',I will do for other tab myself once i got the concept.

mstBCE

tblBCE和mstBCE表之间没有关系，我们只需要遵循从上到下的顺序即可.

There is no relation between tblBCE and mstBCE tables,we need to follow the top to bottom sequence only.

在前端应用程序中，我仅按顺序显示这些数据，即

In front end application i display these data only by sequence i.e.

我按照两个表的顺序来显示数据，例如标签"tab display text111111 BCE"的注释值应为comment111111，标签"tab display text222222 BCE"的注释值应为"comments22222"等.

i follow the sequence of both table to display data like for label "tab display text111111 BCE" comment value should be comments111111 and for label "tab display text222222 BCE" should be "comments22222" etc.

在前端，它显示为一个用户，如下所示:

In front end it display as given below for one user:

所以输出应该是.

输出

谢谢

推荐答案

我的第一个想法是改进架构，您是否真的需要这样做.

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

为简化问题，您似乎想基于对mstBCE的联接来设置列名. 您不需要关系，因为tblBCE中的列数是固定的.而是使用动态sql设置从mstBCE透视到一行上选择的列名称.

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); 

这篇关于需要T-SQL帮助才能将多个表中的行转换为Sql Server 2005中具有值的列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！