sql交叉表问题

Question

这是非常详细的问题：这个日期只属于 1 个表

custcode  address
cust1     capitol, cebu city
cust1     gen. maxilom, cebu city
cust1     guadalupe, cebu city
cust2     paknaan, mandaue city
cust2     basak, mandaue city
cust3     lapu-lapu city

在我的报告中我想在我的报告查看器中包含这个字段

customer name  location1           location2                location3
cust1          capitol, cebu city  gen. maxilom, cebu city  guadalupe, cebu city
cust2          paknaan, mandaue    basak, mandaue           lapu-lapu city

请帮忙..

Answer 1

如果您没有向我们展示一个位置字段，该字段可以区分每列中的内容

SELECT 
custcode, 
MAX(CASE WHEN location = 1 THEN address END) AS location1,
MAX(CASE WHEN location = 2 THEN address END) AS location2,
MAX(CASE WHEN location = 3 THEN address END) AS location3
FROM X
GROUP BY custcode

如果您依赖于行排序 SQL Server 特定答案。

这假设您有一个 ID 字段，可以从中计算“第一”行的顺序。

with X as
(
SELECT 1 AS ID, 'cust1' AS  custcode, 'capitol, cebu city' AS address 
UNION ALL
SELECT 2 AS ID,  'cust1' AS  custcode, 'gen. maxilom, cebu city' AS address 
UNION ALL
SELECT 3 AS ID,  'cust1' AS  custcode, 'guadalupe, cebu city' AS address 
UNION ALL
SELECT 4 AS ID,  'cust2' AS  custcode, 'paknaan, mandaue city' AS address 
UNION ALL
SELECT 5 AS ID,  'cust2' AS  custcode, 'basak, mandaue city' AS address 
UNION ALL
SELECT 6 AS ID,  'cust2' AS  custcode, 'lapu-lapu city'
)
, Y AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY custcode ORDER BY ID) AS RN, 
       custcode,
       address 
FROM X
)

SELECT custcode, [1] AS location1 , [2] AS location2,[3] AS location3 FROM Y
PIVOT  
(  
Max(address)  
FOR RN IN ([1], [2],[3])  
) AS PivotTable;