SQL Server 多行合并成一行问题
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
希望能够有幸学习一下各位老师的代码,在这里先提前谢过各位老师啦
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
希望能够有幸学习一下各位老师的代码,在这里先提前谢过各位老师啦
...全文
相关推荐
Sql function 多行中的列合并为一行一列的方法
Sql function 多行中的列合并为一行一列的方法,需要的朋友可以参考下。
Sql Server:多行合并成一行,并做分组统计的两个方法
Sql Server:多行合并成一行,并做分组统计的两个方法,需要的朋友可以参考一下
sql server多行数据拼接的实例方法
sql server多行数据拼接的实例方法,需要的朋友可以参考一下
很有意思的SQL多行数据拼接
今天为了实现一个很原始的问题上网查资料,见到一个很有意思的SQL语句,和大家分享一下
在SQL Server中将多行合并为一行
听起来您需要“透视”使用PIVOT sql命令可以旋转和重组数据,尽管我不确定您是否可以将它作为静态查询用于n列。在标记表中需要一个额外的id,因此每个ID的每个标记也都有自己的顺序。 所以主要...


疑难问题
