طريقة سهلة لتحويل صفوف SQL والأعمدة

في حين يبدو أن وظيفة محور SQL مناسبة لتحويل الصف والعمود ، يمكن أن يكون تعقيدها باهظًا. إذا كنت ترغب في تحقيق ذلك بطريقة أسهل ، فكر في البديل التالي:

استخدم Union All ، والوظائف الإجمالية ، وبيانات الحالة

تستخدم هذه الطريقة Union All لتوسيع البيانات ثم تستخدم الوظيفة الإجمالية وبيان الحالة للمنظور:

SELECT name,
  SUM(CASE WHEN color = 'Red' THEN value ELSE 0 END) AS Red,
  SUM(CASE WHEN color = 'Green' THEN value ELSE 0 END) AS Green,
  SUM(CASE WHEN color = 'Blue' THEN value ELSE 0 END) AS Blue
FROM
(
  SELECT color, Paul AS value, 'Paul' AS name
  FROM yourTable
  UNION ALL
  SELECT color, John AS value, 'John' AS name
  FROM yourTable
  UNION ALL
  SELECT color, Tim AS value, 'Tim' AS name
  FROM yourTable
  UNION ALL
  SELECT color, Eric AS value, 'Eric' AS name
  FROM yourTable
) AS src
GROUP BY name

تفكيك ومنظور ثابت

إذا كنت تعرف القيمة التي تريد تحويلها ، فاستخدم القيم المتشددين للتفكيك والمنظور:

SELECT name, [Red], [Green], [Blue]
FROM
(
  SELECT color, name, value
  FROM yourTable
  UNPIVOT
  (
    value FOR name IN (Paul, John, Tim, Eric)
  ) AS unpiv
) AS src
PIVOT
(
  SUM(value)
  FOR color IN ([Red], [Green], [Blue])
) AS piv

منظور ديناميكي

للحصول على عدد غير معروف من الأعمدة والألوان ، استخدم SQL الديناميكي لإنشاء قائمة منظور تفكيكها:

DECLARE @colsUnpivot AS NVARCHAR(MAX),
    @query AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX)

SELECT @colsUnpivot = STUFF((SELECT ','   QUOTENAME(C.name)
         FROM sys.columns AS C
         WHERE C.object_id = OBJECT_ID('yourtable') AND
               C.name  'color'
         FOR XML PATH('')), 1, 1, '')

SELECT @colsPivot = STUFF((SELECT ','
                         QUOTENAME(color)
                     FROM yourtable AS t
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)')
        , 1, 1, '')

SET @query = 'SELECT name, '   @colsPivot   '
  FROM (
    SELECT color, name, value
    FROM yourtable
    UNPIVOT
    (
      value FOR name IN ('   @colsUnpivot   ')
    ) AS unpiv
  ) AS src
  PIVOT
  (
    SUM(value)
    FOR color IN ('   @colsPivot   ')
  ) AS piv'

EXEC(@query)

جميع الطرق الثلاث تنتج النتائج التالية: