Reputation: 389
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
I want 3 columns named Factory1, Factory2, Factory3 when the value of FactoryName is "ANANTA","ATL" & "ESBL" respectively.The result should be like
NULL
Factory2
ATL
Can I do this using case or something else? How?
Upvotes: 1
Views: 75
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