Sql-Server中的交叉表以特定方式打印 [英] Cross tab in Sql-Server to print specific manner

问题描述

在SQL中需要帮助



我在这样的表中有记录

Need Help In SQL

I have records in table like this

ID	 	Customer	OrderDat                Remarks
1		ABC		2007-01-01 		aaaaaaaaaa
2		ABC		2007-01-02 		bbbbbbbbbb
3		ABC		2007-01-03  	        cccccccccccc
4		DEF		2007-01-02 		dddddddddd

我想在Crystal Report中打印记录如下：

I want to print Record in Crystal Report like this :

OrderID	 Customer Date1     Remark1	    Date2	      Remark2     Date3 	   Remark3
1	ABC     2007-01-01	 aaaaaaaaa  2007-01-02 bbbbbbbbbb  2007-01-03    ccccccc
2	DEF     2007-01-02 dddddddddd    -	        -   	-	    -

先谢谢你..

Thanks in Advance..

推荐答案

您需要使用类似于下面的查询作为Crystal Report的来源：

You need to use query similar to below one as a source of Crystal Report:

DECLARE @customers TABLE (ID INT IDENTITY(1,1), Customer VARCHAR(30), OrderDat DATETIME, Remarks VARCHAR(300))

INSERT INTO @customers (Customer, OrderDat, Remarks)
VALUES('ABC', '2007-01-01', 'aaaaaaaaaa'),
('ABC', '2007-01-02', 'bbbbbbbbbb'),
('ABC', '2007-01-03', 'cccccccccccc'),
('DEF', '2007-01-02', 'dddddddddd')


SELECT T1.Customer, T1.[1] AS Date1,  T2.[1] AS Remarks1, T1.[2] AS Date2, T2.[2] AS Remarks2, T1.[3] AS Date3, T2.[3]  AS Remarks3
FROM (
    SELECT Customer, [1], [2], [3]
    FROM (
        SELECT Customer, CONVERT(VARCHAR(10),OrderDat,121) AS OrderDat, ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY OrderDat) AS EventID
        FROM @customers
    ) AS DT1
    PIVOT(MAX(OrderDat) FOR EventID IN([1], [2], [3])) AS PT1) AS T1
    INNER JOIN (
        SELECT Customer, [1], [2], [3]
        FROM (
        SELECT Customer, Remarks, ROW_NUMBER() OVER(PARTITION BY Customer ORDER BY OrderDat) AS EventID
        FROM @customers
        ) AS DT2
        PIVOT(MAX(Remarks) FOR EventID IN([1], [2], [3])) AS PT2) AS T2
    ON T1.Customer = T2.Customer

结果：

Result:

ABC	2007-01-01	aaaaaaaaaa	2007-01-02	bbbbbbbbbb	2007-01-03	cccccccccccc
DEF	2007-01-02	dddddddddd	NULL	NULL	NULL	NULL

但是我需要警告你：这是无效的非 - 优雅的方式！

But i need to warn you: This is ineffective and non-elegant way!

这篇关于Sql-Server中的交叉表以特定方式打印的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！