亂馬客

--資料1
declare @value1 varchar(20);
set @value1 = '這是亂馬客~~~';

--資料2
declare @value2 varchar(20);
set @value2 = '資料2開頭';

--要長度90的字串
declare @fixedLen int;
set @fixedLen = 30;
select @value1 + SPACE(@fixedLen-datalength(@value1)) + ']' as result
union 
select @value2 + SPACE(@fixedLen-datalength(@value2)) + ']';

IF OBJECT_ID (N'dbo.ufn_GetFixedStr') IS NOT NULL
   DROP FUNCTION dbo.ufn_GetFixedStr
GO

CREATE FUNCTION dbo.ufn_GetFixedStr (@value varchar(8000), @fixedLen int)
RETURNS varchar(8000)
AS
BEGIN
	declare @result varchar(8000);
	set @result = @value + SPACE(@fixedLen-datalength(@value));
	RETURN @result;
END
GO

use tempdb
go

CREATE TABLE Product
( 
ItemNo VARCHAR(30), 
ItemName NVARCHAR(90),
Price MONEY)
GO

CREATE TABLE OrderDetail
(id INT,
ItemNo VARCHAR(30), 
Qty INT )
GO

CREATE TABLE [Order]
(
id INT, 
CustName NVARCHAR(30)
)
GO
delete from Product;

INSERT INTO Product( ItemNo, Price, ItemName)
VALUES( '0001', 100, N'大頭狗');

INSERT INTO Product( ItemNo, Price, ItemName)
VALUES( '0002', 200, N'Surface Pro 3');

INSERT INTO Product( ItemNo, Price, ItemName)
VALUES( '0003', 300, N'自拍神器');

delete from [Order];
INSERT INTO [Order](id, CustName)
VALUES( 1, N'亂馬客' );

INSERT INTO [Order](id, CustName)
VALUES( 2, N'Katusa' );

delete from OrderDetail;
INSERT INTO OrderDetail(id, ItemNo, Qty)
VALUES( 1, '0001', 1);

INSERT INTO OrderDetail(id, ItemNo, Qty)
VALUES( 1, '0002', 2);

INSERT INTO OrderDetail(id, ItemNo, Qty)
VALUES( 1, '0003', 4);

INSERT INTO OrderDetail(id, ItemNo, Qty)
VALUES( 2, '0001', 1);

INSERT INTO OrderDetail(id, ItemNo, Qty)
VALUES( 2, '0002', 2);

INSERT INTO OrderDetail(id, ItemNo, Qty)
VALUES( 2, '0003', 5);

IF OBJECT_ID (N'dbo.ufn_GetFixedStr') IS NOT NULL
   DROP FUNCTION dbo.ufn_GetFixedStr
GO

CREATE FUNCTION dbo.ufn_GetFixedStr (@value varchar(8000), @fixedLen int)
RETURNS varchar(8000)
AS
BEGIN
	declare @result varchar(8000);
	set @result = @value + SPACE(@fixedLen-datalength(@value));
	RETURN @result;
END
GO
            
;
WITH cte_Orders
AS
(
SELECT o.id, o.CustName, p.ItemNo, p.Price, p.ItemName , od.Qty, od.Qty * p.Price AS Total
, SUM(od.Qty * p.Price) OVER (PARTITION BY o.id) AS OrderTotal
FROM [Order] o INNER JOIN OrderDetail od
ON o.id = od.id 
INNER JOIN  Product p  
ON p.ItemNo = od.ItemNo
) 
SELECT DISTINCT
        id ,
        CustName ,
		OrderTotal,
        STUFF(( SELECT ',' + dbo.ufn_GetFixedStr(ItemNo , 10)  
									+ dbo.ufn_GetFixedStr(cast(ItemName as varchar(30)), 30)
									+ dbo.ufn_GetFixedStr(cast(Qty as varchar(10)), 10)
									+ dbo.ufn_GetFixedStr(cast(Price as varchar(10)), 10)
									+ dbo.ufn_GetFixedStr(cast(Total as varchar(13)), 10)
                FROM    cte_Orders b
                WHERE   a.id = b.id
              FOR
                XML PATH('')
              ), 1, 1, '') AS [details]
		
FROM cte_Orders a;
 

-- 因為 3 聯式，所以重覆 3個資料出來 

WITH cte_Orders
AS
(
SELECT o.id, o.CustName, p.ItemNo, p.Price, p.ItemName , od.Qty, od.Qty * p.Price AS Total
, SUM(od.Qty * p.Price) OVER (PARTITION BY o.id) AS OrderTotal
FROM [Order] o INNER JOIN OrderDetail od
ON o.id = od.id 
INNER JOIN  Product p  
ON p.ItemNo = od.ItemNo
) 
, cte_Group
AS
(
SELECT 1 AS GroupNo
UNION ALL
SELECT GroupNo + 1
FROM cte_Group
WHERE GroupNo < 3
)
SELECT o.id, o.CustName, o.OrderTotal, g.GroupNo
, REPLACE(o.details, ',', CHAR(10) + CHAR(13)) AS details
FROM (SELECT DISTINCT
        id ,
        CustName ,
		OrderTotal,
        STUFF(( SELECT ',' + dbo.ufn_GetFixedStr(ItemNo , 10)  
									+ dbo.ufn_GetFixedStr(cast(ItemName as varchar(30)), 30)
									+ dbo.ufn_GetFixedStr(cast(Qty as varchar(10)), 10)
									+ dbo.ufn_GetFixedStr(cast(Price as varchar(10)), 10)
									+ dbo.ufn_GetFixedStr(cast(Total as varchar(13)), 10)
                FROM    cte_Orders b
                WHERE   a.id = b.id
              FOR
                XML PATH('')
              ), 1, 1, '') AS [details]
		
	FROM cte_Orders a) o
	CROSS APPLY cte_Group g;
 
-- clear testing
-- drop table Product
-- drop table [Order]
-- drop table OrderDetail