CODEWITHSUNDEEP
sqldatabasesql-server-2012
Mimi
Mimi

Reputation: 389

How can I split a column into different columns

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

  1. ANANTA
  2. ANANTA
  3. ATL
  4. ESBL
  5. ATL
  6. ATL

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

Upvotes: 1

Views: 75

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93754

Use Pivot to transpose the data

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

Upvotes: 2

Related Questions