SQL Server 将UNPIVOT与其他select语句组合

vhipe2zx 于 2023-01-29 发布在其他

关注(0)|答案(2)|浏览(155)

我已经在SQL Server中创建了一个名为dba.pp_Datasource_IL1201_Auto_Vehicles的表值函数。生成的动态数据将流向PDF表单。当我只查询具有UNPIVOT运算符的第二个选择时，数据将完全正确地流向PDF，并在SSMS中显示为所需的内容。但是，当我添加其他列时，出现此错误
子查询返回了多个值。当子查询跟在=、!=、〈、〈=、〉、〉=后面或子查询用作表达式时，不允许出现这种情况。
预期结果：
| 色谱柱A|B栏|
| - ------|- ------|
| 数据1|UNPIV数据|
| 零|UNPIV数据|
| 零|UNPIV数据|
| 零|UNPIV数据|
| 零|UNPIV数据|
| 零|UNPIV数据|
| 零|UNPIV数据|
| 零|UNPIV数据|
| 零|UNPIV数据|
| 零|UNPIV数据|

SELECT book_veh_num,
(select description

        FROM
            (
             SELECT
            convert(varchar(255), veh_status) as veh_status,
            convert(varchar(255), veh_num) as veh_num,
            convert(varchar(255), veh_year) as veh_year,
            convert(varchar(255), veh_make) as veh_make,
            convert(varchar(255), veh_model) as veh_model,
            convert(varchar(255), veh_vin) as veh_vin,
            convert(varchar(255), veh_cost_new) as veh_cost_new,
            convert(varchar(255), veh_garage_loc) as veh_garage_loc,
            convert(varchar(255), veh_class_code) as veh_class_code,
            convert(varchar(255), ' ') as blank_line
             FROM dba.pp_Datasource_IL1201_Auto_Vehicles(8589100, 'BusAuto')
            ) d
UNPIVOT
( description for vehicle in
(veh_status, veh_num, veh_year, veh_make, veh_model, veh_vin, veh_cost_new, veh_garage_loc, veh_class_code, blank_line)
) unpiv)

FROM dba.pp_Datasource_IL1201_Auto_Vehicles(8589100, 'BusAuto')

sql-server

来源：https://stackoverflow.com/questions/75260239/combining-unpivot-with-other-select-statements

2条答案

按热度按时间

km0tfn4u1#

我怀疑问题是您使用了子查询，而不是派生表。这是不可能测试的，因为我们没有样本数据，但也许这就是您想要的：

SELECT description, vehicle
FROM (SELECT CONVERT(varchar(255), veh_status) AS veh_status,
             CONVERT(varchar(255), veh_num) AS veh_num,
             CONVERT(varchar(255), veh_year) AS veh_year,
             CONVERT(varchar(255), veh_make) AS veh_make,
             CONVERT(varchar(255), veh_model) AS veh_model,
             CONVERT(varchar(255), veh_vin) AS veh_vin,
             CONVERT(varchar(255), veh_cost_new) AS veh_cost_new,
             CONVERT(varchar(255), veh_garage_loc) AS veh_garage_loc,
             CONVERT(varchar(255), veh_class_code) AS veh_class_code,
             CONVERT(varchar(255), ' ') AS blank_line
      FROM dba.pp_Datasource_IL1201_Auto_Vehicles(8589100, 'BusAuto') ) d
    UNPIVOT (description
             FOR vehicle IN (veh_status, veh_num, veh_year, veh_make, veh_model, veh_vin, veh_cost_new, veh_garage_loc, veh_class_code, blank_line)) unpiv;

或者，您可以使用VALUES表构造来取消透视数据：

SELECT V.ColumnValue,
       V.ColumnName
FROM dba.pp_Datasource_IL1201_Auto_Vehicles(8589100,N'BusAuto')DIAV
     CROSS APPLY (VALUES(CONVERT(varchar(255), veh_status),N'veh_status'),
                        (CONVERT(varchar(255), veh_num),N'veh_num'),
                        (CONVERT(varchar(255), veh_year),N'veh_year'),
                        (CONVERT(varchar(255), veh_make),N'veh_make'),
                        (CONVERT(varchar(255), veh_model),N'veh_model'),
                        (CONVERT(varchar(255), veh_vin),N'veh_vin'),
                        (CONVERT(varchar(255), veh_cost_new),N'veh_cost_new'),
                        (CONVERT(varchar(255), veh_garage_loc),N'veh_garage_loc'),
                        (CONVERT(varchar(255), veh_class_code),N'veh_class_code'),
                        (CONVERT(varchar(255), ' '),N'blank_line'))V(ColumnValue,ColumnName);

赞(0）回复(0）举报 2023-01-29

h7appiyu2#

下面是一个包含一些数据的透视查询示例。我猜您正在执行相关子查询以获得正确的强制转换，但您可以在子查询中准备所有内容：

drop table #car

create table #car (vin int, make varchar(30), color varchar(30), cost int)

insert into #car (vin, make, color, cost)
select 1234, 'BMW', 'Red', 99999
union
select 1235, 'Mercedes', 'Blue', 100000
union
select 1236, 'Volvo', 'Silver arrow', 3000

select vin, vehicle, description
from (
    select vin, make, color, cast(cost as varchar(30)) AS cost
    from #car
    ) x
unpivot (description for vehicle in (make, color, cost)) v

赞(0）回复(0）举报 2023-01-29

我来回答

SQL Server 将UNPIVOT与其他select语句组合

2条答案

相关问题

热门标签

最新问答