如何根据SQL Server中的类型PIVOT数据

本文介绍了如何根据SQL Server中的类型PIVOT数据的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

这是我的数据 实际预算类型 90 100 A 125 130 A 100 110 B $ b $ 100 222 B 150 150 B 我要这样的 A_BUDG B_BUDG A_ACT B_ACT 100 110 90 100 130 222 125 100 150 150

这里A_BUDG预算和A_ACT为一个实际的 请给我建议。 我尝试了什么： 这是我的第一个sql qry

SELECT R. * FROM （选择D.ACTUAL，D.BUDGET，'A'类型来自RENT_T_PARKING_HDR H 左加入RENT_T_PARKING_DTL D ON H.VOUCHERNO = D.VOUCHERNO 3和4之间的时间表和3和4之间的时间表 UNION 选择D.ACTUAL，D.BUDGET，'B'作为类型来自RENT_T_PARKING_HDR H 左加入RENT_T_PARKING_DTL D ON H.VOUCHERNO = D.VOUCHERNO 时间1和2之间的时间和1和2之间的时间 ）R GROUP BY R.TYPE ，R.ACTUAL，R.BUDGET ORDER BY TYPE

需要帮助以获得所需的输出

解决方案

SQL server的 PIVOT 将数据行展平为列的函数。该语句的语法至少有一个 unpivoted 列，然后是多个转向列，基于该列（未转换的列）。 @digimanus 有一篇关于如何创建 Dynamic Pivot 的文章，该文章使用数据 CUBE 对于某些数据集可能需要的更高级查询。 对于您的特定问题，我无法确定枢轴点是什么;所以我无法提供代码答案。您提供的数据似乎不完整;你正在显示一个数据表，但你的查询引用了两个单独的表。 引用 MSDN ：使用PIVOT和UNPIVOT - SQL Server | Microsoft Docs [ ^ ] Digimanus文章：在SQL Server 2005,2008和2008 R2中使用多维数据集和事件处理程序进行动态透视 [ ^ ]

This is my data ACTUAL BUDGET TYPE 90 100 A 125 130 A 100 110 B 100 222 B 150 150 B And i want like this A_BUDG B_BUDG A_ACT B_ACT 100 110 90 100 130 222 125 100 150 150

here A_BUDG FOR A BUDGET AND A_ACT FOR A ACTUAL Please give me suggestion. What I have tried: this is my sql qry for first one

SELECT R.* FROM ( select D.ACTUAL,D.BUDGET,'A' AS TYPE from RENT_T_PARKING_HDR H left join RENT_T_PARKING_DTL D ON H.VOUCHERNO=D.VOUCHERNO WHERE TIMEFROM BETWEEN 3 AND 4 AND TIMETO BETWEEN 3 AND 4 UNION select D.ACTUAL,D.BUDGET,'B' AS TYPE from RENT_T_PARKING_HDR H left join RENT_T_PARKING_DTL D ON H.VOUCHERNO=D.VOUCHERNO WHERE TIMEFROM BETWEEN 1 AND 2 AND TIMETO BETWEEN 1 AND 2 )R GROUP BY R.TYPE,R.ACTUAL, R.BUDGET ORDER BY TYPE

and need help for desired output

解决方案

SQL server has a PIVOT function which "flattens" out rows of data into columns. The syntax for the statement has at least one unpivoted columns and then multiple columns which are pivoted based on that (unpivoted column). @digimanus has an article on how to create a Dynamic Pivot which utilizes a data CUBE for more advanced queries which may be needed with some datasets. For your particular question, I cannot determine what the pivot point is; so I cannot provide a code-answer. The "data" you have provided seems to be incomplete; you are showing one data table but the queries you have are referencing two separate tables. References MSDN: Using PIVOT and UNPIVOT - SQL Server | Microsoft Docs[^] Digimanus article: Dynamic Pivoting with Cubes and eventhandlers in SQL Server 2005, 2008 and 2008 R2[^]