SQL Pivot - Union 2 queries

Question

我有以下查询，该查询创建了一个枢轴表，第一行显示了布大小：s，m，l和xl。

SELECT * 
FROM (SELECT 
        [LOC],
        [QUANTITY],
        [SIZE]
  FROM [TABLE]
  WHERE [Item]= '123456') s 
  PIVOT (SUM([QUANTITY]) FOR [SIZE] IN ([S],[M],[L],[XL])) pvt

导致：

  LOC       S   M   L   XL
  USA       2   2   1
  EUR       1   2   2   1
  ASIA      1   2   2   1

由于某些产品具有不同的尺寸表(例如：男人有：s，m，l。女人有：36,38,40(，所以我想将上述查询与该查询结合在一起，这显示了可用尺寸的指定的产品。因此，第一行是基于台式尺寸的可用尺寸。

  SELECT * FROM [TABLE-Sizing] WHERE [Item] = '123'

结果：

  34
  36
  38

我希望有人可以帮助我！

谢谢！

[edit1]我试图与第二个查询一起使用联合，但不知道在最后一行中插入什么来使用它：

SELECT * 
FROM (SELECT 
        [LOC],
        [QUANTITY],
        [SIZE]
  FROM [TABLE]
  WHERE [Item]= '123456')
  UNION
  SELECT [SIZE]
  FROM [TABLE-SIZING] s 
  PIVOT (SUM([QUANTITY]) FOR [SIZE] IN (?????)) pvt

Answer 1

就像提到的您首先需要一个可能性列表：

我将所有这些都将所有这些结合到一个CTE中，并用它代替表格语句

--I used a classic stuff technique combined with quotename which brackets the answers
declare @possibilities varchar(max);
;with cte as (
select distinct size from table
union all
select distinct size from [different table]
)
select @possibilities = stuff( (select distinct ','+QUOTENAME(SIZE) from cte for xml path(''))
                ,1,1,'')
--Now create dynamic SQL
declare @sql varchar(max);
set @sql = 
'SELECT * 
FROM (SELECT 
        [LOC],
        [QUANTITY],
        [SIZE]
  FROM [TABLE]
  WHERE [Item]= '123456') s 
  PIVOT (SUM([QUANTITY]) FOR [SIZE] IN ('
+ @possibilities
+ ') pvt'
EXECUTE(@sql)