如何将一列分割成不同的列

I have varchar column with 3 types of values. I want to split it into 3 columns base on the types of value. For example my column is, FactoryName of values

我有varchar列，有3种类型的值。我想根据值的类型把它分成3列。例如，我的列是，value的FactoryName

1. ANANTA
2. ANANTA
3. ANANTA
4. ANANTA
5. ATL
6. ATL
7. ESBL
8. ESBL
9. ATL
10. ATL
11. ATL
12. ATL

I want 3 columns named Factory1, Factory2, Factory3 when the value of FactoryName is "ANANTA","ATL" & "ESBL" respectively.The result should be like

当FactoryName的值分别为“ANANTA”、“ATL”和“ESBL”时，我想要3列分别为Factory1、Factory2、Factory3。结果应该是这样的

• Factory1
• Factory1
• ANANTA
• ANANTA
• ANANTA
• ANANTA
• NULL
• 零
• NULL
• 零
• NULL
• 零

• NULL

  零

• Factory2

  Factory2

• NULL
• 零
• NULL
• 零
• ATL
• ATL
• NULL
• 零
• ATL
• ATL

• ATL

  ATL

  Can I do this using case or something else? How?

  我能用这个用例还是别的什么?如何?

1 个解决方案

#1

Use Pivot to transpose the data

使用Pivot来转置数据。

CREATE TABLE #comp
  (
     name VARCHAR(50)
  )

INSERT #comp
VALUES ('ANANTA'),('ANANTA'),('ATL'),
       ('ESBL'),('ATL'),('ATL')

SELECT [ANANTA] AS factory1,
       [ATL]    AS factory2,
       [ESBL]   AS factory3
FROM   #comp
       PIVOT (Max(name)
             FOR name IN ([ANANTA],
                          [ATL],
                          [ESBL])) piv 

or conditional Aggregate

或有条件的总

select  max(case when name = 'ANANTA' then name end) factory1,
        max(case when name = 'ATL' then name end) factory2,
        max(case when name = 'ESBL' then name end) factory3 
from #comp

Update: If you don't want show the result as single row then remove max aggregate

更新:如果您不想显示单个行的结果，那么删除最大聚合

SELECT CASE WHEN name = 'ANANTA' THEN name END factory1,
       CASE WHEN name = 'ATL' THEN name END factory2,
       CASE WHEN name = 'ESBL' THEN name END factory3
FROM   #comp