sql - 有没有更好的方法来使用相同的源表构建表

Question

这段代码有效，但我想知道是否有更好的方法来做到这一点？对于每个 Select 语句，我从同一个源中提取并最终尝试从具有多行（EAV 样式）的表移动到类似条目的列中。这是目前表格结构方式的图像： 当前表格结构 这是我想要结束的图像： 尝试创建的新结构

       SELECT * FROM
(SELECT 
        t1.a, 
        t1.b, 
        t1.c As c1, 
        t2.d AS d2, 
        t2.c As c2 
        FROM
(SELECT [Code],
      [DBName].[dbo].[Spec].SpecItem_Code,
      [DBName].[dbo].[SpecItem].Name,
      [DBName].[dbo].[Spec].SpecValue
  FROM [DBName].[dbo].[Vehicle]
  LEFT JOIN [DBName].[dbo].[Spec] ON [DBName].[dbo].[Vehicle].Code=[DBName].[dbo].[Spec].Code
  LEFT JOIN [DBName].[dbo].[SpecItem] ON [DBName].[dbo].[SpecItem].[Code] = [DBName].[dbo].[Spec].[SpecItem_Code]
  WHERE
  [DBName].[dbo].[SpecItem].Name LIKE '%test1%') t1
  FULL OUTER JOIN
  (SELECT 
  [DBName].[dbo].[Vehicle].[Code],
      [DBName].[dbo].[Spec].SpecItem_Code,
      [DBName].[dbo].[SpecItem].Name,
      [DBName].[dbo].[Spec].SpecValue
      FROM [DBName].[dbo].[Vehicle]
  LEFT JOIN [DBName].[dbo].[Spec] ON [DBName].[dbo].[Vehicle].Code=[DBName].[dbo].[Spec].Code
  LEFT JOIN [DBName].[dbo].[SpecItem] ON [DBName].[dbo].[SpecItem].[Code] = [DBName].[dbo].[Spec].[SpecItem_Code]
  WHERE
  [DBName].[dbo].[SpecItem].Name LIKE '%test2%') t2
  ON t1.Code = t2.Code) t12
    FULL OUTER JOIN
  (SELECT 
  [DBName].[dbo].[Vehicle].[Code],
      [DBName].[dbo].[Spec].SpecItem_Code,
      [DBName].[dbo].[SpecItem].Name,
      [DBName].[dbo].[Spec].SpecValue
      FROM [DBName].[dbo].[Vehicle]
 LEFT JOIN [DBName].[dbo].[Spec] ON [DBName].[dbo].[Vehicle].Code=[DBName].[dbo].[Spec].Code
 LEFT JOIN [DBName].[dbo].[SpecItem] ON [DBName].[dbo].[SpecItem].[Code] = [DBName].[dbo].[Spec].[SpecItem_Code]
 WHERE
  [DBName].[dbo].[SpecItem].Name LIKE '%test3%') t3
  ON t12.Code = t3.Code

标签： sqlsql-serverjoinselectssms