SQL Server 多行合并成一行问题

sqlstartfromzero sqlstartfromzero 墨衍会员 · AI 创作全网分发 墨衍智能分发 通过墨衍一键同步至各平台 可同步平台 CSDN微信公众号微博知乎掘金百家号博客园抖音小红书 你的文章也可以这样分发 墨衍会员 ¥399起/年,写一次发全网 我也要 + 加入社区
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

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

相关推荐

评论 5
收藏 已收藏
分享
返回顶部
sqlstartfromzero
疑难问题 22,298 成员 121,727 内容