Click here to Skip to main content
15,912,400 members
Please Sign up or sign in to vote.
1.00/5 (2 votes)
See more:
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



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..
Posted
Updated 6-May-14 1:52am
v2

1 solution

You need to use query similar to below one as a source of Crystal Report:
SQL
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!
 
Share this answer
 
Comments
chetna2810 7-May-14 4:27am    
Thanks a lot sir..:)
Maciej Los 7-May-14 5:48am    
You're welcome ;)

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900