如何在SQL Server中使用Pivot [英] How to use Pivot In SQL Server

问题描述

Hello Experts，

查看Query，

Hello Experts,
Have a look on Query,

select 
	c.DocNum as PR#, 
	a.CardCode as SupplierCode,
	a.CardName as SupplierName,
	a.DocDate as QutationDate,
	a.DocNum as QutationNum
from OPQT a
        Inner join PQT1 b on a.DocEntry = b.DocEntry
        Inner Join [@OPRQ] c on c.DocNum =b.U_ReqRef
        Inner Join [@PRQ1] d on d.DocEntry = c.DocEntry
        AND b.ItemCode = d.U_ItemCode AND a.U_PrSer = c.Series
        inner join OCRD e on a.CardCode = e.CardCode
        Inner Join [@DEPT] f on f.Code = c.U_DepartmentCode
        Where   a.DocNum in ('257','258')

这是结果，

And Here is the Results,

PR# SupplierCode    SupplierName    QutationDate    QutationNum
4101    QT-00002    QAISER TRADER   2014-01-15         257
4101    QT-00002    QAISER TRADER   2014-01-15         257
4101    HA-00001    HYDER ALI & CO. 2014-01-15         258
4101    HA-00001    HYDER ALI & CO. 2014-01-15         258

what我想要的是显示两行中的数据应该是一些类似的东西，

what i want is to show the data in just two rows that should be some thing like,

PR#	SupplierName	QutationNum	SupplierName	QutationNum
4101	QAISER TRADER	     257	HYDER ALI & CO.	   258
4101	QAISER TRADER	     257	HYDER ALI & CO.	   258

先谢谢，

Thanks in Advance,

推荐答案

我刚写了一篇关于如何使用PIVOT的文章，和替代品。 此处 [ ^ ]。



您的样本数据总是显示两个供应商。如果每行中的供应商数量可变，则pivot将要求您定义最大可能数量。我的文章显示了这样做的方法，允许你的记录数量是任意的。



当你选择两个时，我认为我展示的'案例'解决方案，在这里最适合你。

I just wrote an article on how to use PIVOT, and the alternatives. Here[^].

Your sample data always shows two suppliers. If the number of suppliers in each row is variable, pivot will require you to define a maximum possible number. My article shows ways of doing this that allows your number of records to be arbitrary.

As you're selecting on two, I think the 'case' solution I show, would work best for you here.

这篇关于如何在SQL Server中使用Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！