如何在SQL Server上转动列或执行以下操作: [英] How can I pivot a Column or do the following on SQL Server:
问题描述
Ent_ID Dest_ID Bank_ID Post_ID
12 ---- 800 ---- 1 ------ 2 ------
13 ---- 900 ---- 1 ------ 2 ------
14 ---- 800 ---- 1 --- --- 9 ------
15 ---- 800 ---- 1 ------ 10 -----
我试图以这种方式使用SQL显示这一点,以便Ent_ID(12,13)显示为一行,因为它们的Bank_ID和Post_ID是相同的。
我的最终目标是在Datatable中为winforms返回它并将其转换为datagridview,其中我将Dest_ID数据作为复选框标题列。
Bank_ID - Post_ID-- Dest_ID_800 - Dest_ID_900
1 --------- 2-- ------- 800 ------------ 900
1 --------- 9 ------ --- 800 ------------
1 --------- 10 --------------- --------- 900
你可以创建一个静态的或动态数据透视查询来转换数据。
以下是静态数据透视查询的示例:
DECLARE @ DataTable TABLE (Ent_ID INT ,Dest_ID INT ,Bank_ID INT ,Post_ID INT 跨度>);
INSERT INTO @ DataTable (Ent_ID,Dest_ID,Bank_ID,Post_ID)
VALUES ( 12 ,< span class =code-digit> 800 , 1 , 2 ),
( 13 , 900 , 1 , 2 ),
( 14 , 800 , 1 , 9 ),
( 15 , 900 , 1 , 10 跨度>);
SELECT Bank_ID,Post_ID,[ 800 ] AS Dest_ID_800,[ 900 ] AS Dest_ID_900
FROM
( SELECT Dest_ID,Bank_ID,Post_ID
FROM @ DataTable ) AS SourceTable
PIVOT(MAX(Dest_ID) FOR Dest_ID IN
([ 800 ],[ 900 ])
) AS 数据透视表;
结果:
Bank_ID Post_ID Dest_ID_800 Dest_ID_900
1 2 800 900
1 9 800 NULL
1 10 NULL 900
如果列数(Dest_ID_),静态查询是好的800
,Dest_ID_900
等)是固定的。如果您想要动态列数(基于Dest_ID
列中的数据),您可以编写动态数据透视查询,即您需要重写静态查询以动态写入。
如果你想写一个动态的数据透视查询,请看这里:
1. Sql Server中的动态PIVOT [ ^ ]
2. 动态数据透视查询的示例 [ ^ ]
Ent_ID Dest_ID Bank_ID Post_ID
12---- 800---- 1------ 2------
13---- 900---- 1------ 2------
14---- 800---- 1------ 9------
15---- 800---- 1------ 10-----
I'm trying to display this in such a way using SQL so that Ent_ID (12, 13) shows up as one row because their Bank_ID and Post_ID are same.
My ultimate goal is to return this in Datatable for winforms and translate it to datagridview where I have Dest_ID data as checkbox header column.
Bank_ID--Post_ID-- Dest_ID_800--Dest_ID_900
1--------- 2--------- 800------------900
1--------- 9--------- 800------------
1--------- 10------------------------900
Hi,
You can create a static or dynamic pivot query to transform the data.
Here's an example of the static pivot query:
DECLARE @DataTable TABLE (Ent_ID INT, Dest_ID INT, Bank_ID INT, Post_ID INT); INSERT INTO @DataTable (Ent_ID, Dest_ID, Bank_ID, Post_ID) VALUES (12, 800, 1, 2), (13, 900, 1, 2), (14, 800, 1, 9), (15, 900, 1, 10); SELECT Bank_ID, Post_ID, [800] AS Dest_ID_800, [900] AS Dest_ID_900 FROM (SELECT Dest_ID, Bank_ID, Post_ID FROM @DataTable) AS SourceTable PIVOT (MAX(Dest_ID) FOR Dest_ID IN ([800], [900]) ) AS PivotTable;
Result:
Bank_ID Post_ID Dest_ID_800 Dest_ID_900 1 2 800 900 1 9 800 NULL 1 10 NULL 900
A static query is good if the number of columns (Dest_ID_800
,Dest_ID_900
, etc.) is fixed. If you want dynamic number of columns (based on the data inDest_ID
column), you can write a dynamic pivot query, i.e. you need to rewrite the static query to be dynamically written.
If you want to write a dynamic pivot query, please take a look here:
1. Dynamic PIVOT in Sql Server[^]
2. An example of a dynamic pivot query[^]
这篇关于如何在SQL Server上转动列或执行以下操作:的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!