Pivot with 2 different types of columns

Question

declare @tbl as table
(
    ItemId int,
    ProductDatasheetId int,
    ProductSpecName varchar(50),
    FiledType varchar(50),
    Value_Text varchar(50),
    Value_DateTime datetime,
    Value_Decimal decimal(18,3),
    ProdTestOpt varchar(50)
)

insert into @tbl
values (139, 1, 'Capacitor', 'Numeric', NULL, NULL, 11.000, NULL),
       (139, 1, 'Transistor', 'Numeric', NULL, NULL, 175.000, NULL),
       (139, 1, 'Insert Material', 'Option', NULL, NULL, 0.000, 'Brass'),
       (139, 1, 'Base Diameter', 'Numeric', NULL, NULL, 55.000, NULL),
       (139, 1, 'TestTube', 'Numeric', NULL, NULL, 130.000, NULL)

select 
    ItemId, [Capacitor], [Transistor], [Insert Material],
    [Base Diameter], [TestTube] 
from
    (select * 
     from @tbl 
     where FiledType ='Numeric') as t 
pivot
    (sum(Value_Decimal) 
        for ProductSpecName in ([Capacitor], [Transistor], [Insert Material], [Base Diameter], [TestTube])
    ) as pv

union

select 
    ItemId, [Capacitor], [Transistor], [Insert Material],
    [Base Diameter], [TestTube] 
from
    (select * 
     from @tbl 
     where FiledType = 'Option') as t 
pivot
    (MAX(ProdTestOpt) 
          for ProductSpecName in ([Capacitor], [Transistor], [Insert Material], [Base Diameter], [TestTube])
    ) as pv

I tried to pivot table with text and numeric value but pivot is not possible in single query.

I did them separately and when I try to union it throws an error. is it possible to pivot by text and numeric value at the same time?

Expected output:

ItemId	Capacitor	Transistor	Insert Material	Base Diameter	TestTube
139	11.000	175.000	Brass	55.000	130.000

Answer 1

You need to JOIN, not UNION:

select
    i.ItemId,
    a.Capacitor,
    a.Transistor,
    b.[Insert Material],
    a.[Base Diameter],
    a.TestTube
from
(
    select distinct ItemId from #tbl
) i
left join
(
    select 
        ItemId, [Capacitor], [Transistor], [Base Diameter], [TestTube] 
    from
        (select * 
         from #tbl 
         where FiledType ='Numeric') as t 
    pivot
        (sum(Value_Decimal) 
            for ProductSpecName in ([Capacitor], [Transistor], [Insert Material], [Base Diameter], [TestTube])
        ) as pv
) a on i.ItemId = a.ItemId
left join 
(
    select 
        ItemId, [Insert Material]
    from
        (select * 
         from #tbl 
         where FiledType = 'Option') as t 
    pivot
        (MAX(ProdTestOpt) 
              for ProductSpecName in ([Capacitor], [Transistor], [Insert Material], [Base Diameter], [TestTube])
        ) as pv
) b on i.ItemId = b.ItemId

Gives correct result:

ItemId	Capacitor	Transistor	Insert Material	Base Diameter	TestTube
139	11	175	Brass	55	130

There's probably a more elegant solution too, but this was the easiest thing that came to mind.