Combining UNPIVOT with other select statements-CodePudding

I've created a Table-valued function in SQL server called dba.pp_Datasource_IL1201_Auto_Vehicles. The resulting dynamic data is going to flow to a PDF form. When I just query the second select that has the UNPIVOT operator, the data flows perfectly fine to the PDF and displays as desired in SSMS. However, when I add other columns, I get this error

Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

DESIRED RESULT:

Column A	Column B
DATA 1	UNPIV DATA
NULL	UNPIV DATA
NULL	UNPIV DATA
NULL	UNPIV DATA
NULL	UNPIV DATA
NULL	UNPIV DATA
NULL	UNPIV DATA
NULL	UNPIV DATA
NULL	UNPIV DATA
NULL	UNPIV DATA

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')

CodePudding user response：

I suspect the problem is that you've used a subquery, rather than derived table. This is impossible to test, as we have no sample data, but perhaps this is what you want:

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;

Alternatively, you could unpivot your data with a VALUES table construct:

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);

CodePudding user response：

Here's a sample pivot query with some data. I'm guessing you're doing correlated subquery to get casts correct, but you can instead prepare everything in a subquery:

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