使用数据透视表转换SQL表的形状 [英] Transforming shape of a SQL Table using Pivot

问题描述

我以以下格式在表中存储了一些数据

I have some data stored in a table in the following format

BatchID              EntityChanged             ChangeValue

EERS                  ABC                       DEF
EERS                  ABCD                      XYZ
EERS                  Something                 SomeValue
New Batch             SomethingMore             GHI

可以出现在"EntityChanged"列中的值的列表是有限的，事先已知的并且其中没有空格等.为了争辩，让我们说这个列表是-ABC，ABCD，something，somethingMore

The list of values that can appear in the "EntityChanged" columns is finite, known beforehand and has no spaces etc in it. For the sake of argument let us say that this list is - ABC, ABCD, Something, SomethingMore

然后对于上述数据集，我想要输出

Then for the above dataset I would like an output of

BatchID            ABC              ABCD            Something       SomethingMore
EERS               DEF              XYZ             SomeValue       NULL   
New Batch          NULL             NULL            NULL            GHI

使用Pivot，我只能走这么远了.

Using Pivot I could only go so far.

有人可以帮我按期望的方式分割这些数据吗?

Can someone please help me slice this data in the desired way?

推荐答案

尝试一下:

SELECT BatchID, 
   MAX(CASE WHEN EntityChanged = 'ABC' THEN ChangeValue END) 'ABC',
   MAX(CASE WHEN EntityChanged = 'ABCD' THEN ChangeValue END) 'ABCD',
   MAX(CASE WHEN EntityChanged = 'Something' THEN ChangeValue END) 'Something',
   MAX(CASE WHEN EntityChanged = 'SomethingMore' THEN ChangeValue END) 'SomethingMore'
FROM YourTable t 
GROUP BY BatchID

这篇关于使用数据透视表转换SQL表的形状的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！