SQL Server 多行合并成一行问题

[sqlstartfromzero]

Tables
Customers
Column Data Type Description
PartyID uniqueidentifier Primary Key
Creation datetime Account Creation Date
EmailAddress_Contact varchar(128) Email Address
Party
Column Data Type Description
PartyID uniqueidentifier Primary Key
FName varchar(128) First Name
LName varchar(128) Last Name
City varchar(128) City
StateProvince varchar(2) State/Province
Orders
Column Data Type Description
OrderID int Primary Key
OrderDate datetime Order Date
PaidAmount money Product Amount
PartyID uniqueidentifier Customer ID
ProdType int Product ID
Products
Column Data Type Description
ProdType int Primary Key
ProdDesc varchar(128) Product Description

如上图，现在需要找出所有第一笔订单大于1000美元且拥有Gmail邮箱地址的客户，以及他们第一笔订单的详情，按照订单日期、客户的姓顺序排列。需要输出以下几列：
-First name followed by first letter of last name (John D)
-City followed by comma then state abbreviation (Seattle, WA)
-The year that the account was created in (XXXX)
-Date of first order with the name of the month followed by day then comma and year (January
1, 2014)
-Total amount paid by the customer on the entire order ($X,XXX.XX)
-List of products within the order combined in a single row separated by commas (Nail Polish,
Lotion, ect…)

小弟刚刚接触SQL Server， 正在做练习题（T-SQL）, 想请教各位老师一下这里的字符拼接是用"+"吗？另外最后一列List of Products怎么才能把很多行的内容拼接到一行里呢？

另外这里还要找出客户最后一个订单的日期，如果客户只定过一单（即第一单）的话就写'Not Avaiable'. 请问这里该怎么写呢？
Add column to temp table with the last order date of the customer. This column can be any
data type but the date must be should be in this format DD/MM/YYYY
-Add column with number of days between the first order and last order

希望能够有幸学习一下各位老师的代码，在这里先提前谢过各位老师啦

[LongRui888]

大致上是这样的：

select p.fname,p.lname...
from Customers c
inner join party p
        on c.PartyID = p.PartyID
cross apply
(
select v.*
from 
	(
	select top 1 o.*,sum(o.PaidAmount) over(partition by OrderID) as sumPaidAmount 
	from orders o
	where o.PartyID = c.PartyID 
		  and o.PaidAmount >= 1000
	order by OrderDate  desc
	)v
where v.sumPaidAmount >= 1000
)o
inner join products ps
        on ps.ProdType =o.ProdType 
where c.EmailAddress_Contact like '[e-mail]'

[LongRui888]

另外，你要合并产品，用逗号分隔，可以用xml，写个子查询，或者写个自定义函数

[sqlstartfromzero]

引用 1 楼 yupeigu 的回复:

大致上是这样的：

select p.fname,p.lname...
from Customers c
inner join party p
        on c.PartyID = p.PartyID
cross apply
(
select v.*
from 
	(
	select top 1 o.*,sum(o.PaidAmount) over(partition by OrderID) as sumPaidAmount 
	from orders o
	where o.PartyID = c.PartyID 
		  and o.PaidAmount >= 1000
	order by OrderDate  desc
	)v
where v.sumPaidAmount >= 1000
)o
inner join products ps
        on ps.ProdType =o.ProdType 
where c.EmailAddress_Contact like '[e-mail]'

谢谢老师解答，还有一个小问题，这里同一个客户的第一个订单中可能会有多个商品，比如ID为1的客户的第一笔订单里可能有两个prodtype, 分别是洗发水和沐浴露（占了两行），怎么能创建出“洗发水，沐浴露”这样的一列呢（即每个用户只占一行）。再次感谢老师的帮忙！

[sqlstartfromzero]

引用 2 楼 yupeigu 的回复:

另外，你要合并产品，用逗号分隔，可以用xml，写个子查询，或者写个自定义函数

不好意思刚刚看到，谢谢您！我先来写一个试试看

[LongRui888]

引用 4 楼 sqlstartfromzero 的回复:

引用 2 楼 yupeigu 的回复:

另外，你要合并产品，用逗号分隔，可以用xml，写个子查询，或者写个自定义函数

不好意思刚刚看到，谢谢您！我先来写一个试试看

这里有1个例子，你可以参考：

--3.合并字符串
create table test4 (name varchar(10),mytype varchar(10),cj int )

insert into test4 
values('张三','语文',83),
('张三','数学',65),
('张三','物理',85),
('李四','语文',73),
('李四','数学',69),
('李四','物理',93)


select name,
       stuff((select ','+mytype from test4 t4 where t4.name = test4.name for xml path('')),1,1,'') as mytype,
	   stuff((select ','+cast(cj as varchar) from test4 t4 where t4.name = test4.name for xml path('')),1,1,'') as cj
from test4
group by name
/*
name	mytype	cj
李四	语文,数学,物理	73,69,93
张三	语文,数学,物理	83,65,85
*/