假设您想在dbo.circt_cstdn上加入2次，即您想要
owner tech
rowA a.nm b.nm …
与其将值分成2列，不如将其分为2行（上面的每一行为2行），并添加另一列以说明哪一行是哪一列。请注意，第1.1行和第1.2行具有相同的数据（名称和列除外）
name for
row1.1 nm owner row1.2 nm tech …
然后，您将重点放在所有者和技术人员的“名称”列的最大值上。注意-max函数只是用来欺骗PIVOT（需要一些聚合函数），如果只有一个记录所有者技术行1 nm nm，则可以使用返回相同值的任何聚合函数。
现在，如果我们为您查询
i
1 2
与此交叉连接查询的第一部分
SELECT
count_big(*) as [count_all],
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
data_orgtn_yr
FROM dbo.dvc INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm CROSS JOIN dbo.d GROUP BY awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, d.i
现在，如果Di为1，则使用所有者行；如果Di为2，则使用技术行
SELECT
count_big(*) as [count_all],
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
data_orgtn_yr,
Case
WHEN d.i = 1 THEN 'Owner'
WHEN d.i = 2 THEN 'Tech'
END
FROM dbo.dvc INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm CROSS JOIN dbo.d GROUP BY awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, Case WHEN d.i = 1 THEN ‘Owner’ WHEN d.i = 2 THEN ‘Tech’ END
现在添加nm列。要获得名称，您可以将circt_cstdn加入到circt，如果它是所有者行（di = 1），则加入dvc，如果它是技术行（di = 2）。注意-我在这里尝试了快捷方式，方法是将其置于联接条件中。如果不起作用，请尝试博客发布方式（对circt.circt_cstdn_user_id dvc.circt_cstdn_user_id进行联接，然后使用WHERE子句进行筛选）
SELECT
count_big(*) as [count_all],
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
data_orgtn_yr,
Case
WHEN d.i = 1 THEN 'Owner'
WHEN d.i = 2 THEN 'Tech'
END as PersonType,
circt_cstdn_nm
FROM dbo.dvc INNER JOIN dbo.circt on dvc.circt_nm = circt.circt_nm CROSS JOIN dbo.d INNER JOIN dbo.circt_cstdn on circt_cstdn_user_id = CASE WHEN d.i = 1 THEN circt.circt_cstdn_user_id WHEN d.i = 2 THEN dvc.circt_cstdn_user_id END GROUP BY awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, Case WHEN d.i = 1 THEN ‘Owner’ WHEN d.i = 2 THEN ‘Tech’ END, circt_cstdn_nm
使用该视图创建一个视图并创建索引
create VIEW vw_lookup_test_imed
WITH SCHEMABINDING AS <
spell to create INDEX
现在，您将PIVOT转换为PersonType列到Owner和Tech列
SELECT
count_all,
awc_txt,
city_nm,
str_nm,
stru_no,
dvc.circt_nm,
data_orgtn_yr,
[Owner],
[Tech]
FROM ( SELECT count_all, awc_txt, city_nm, str_nm, stru_no, dvc.circt_nm, data_orgtn_yr, PersonType, circt_cstdn_nm FROM dbo.vw_lookup_test_imed WITH (NOEXPAND) ) src PIVOT ( MAX(circt_cstdn_nm) FOR PersonType IN ([Owner], [Tech]) ) pvt
602392714
清零编程群