CODEWITHSUNDEEP
sqlsql-serverpivot
sumit
sumit

Reputation: 15464

pivoting from comma delimited column value in sql server

I need to work on old poorly design database

It has table like below

CREATE TABLE answers (ID INT, ans NVARCHAR(300))
INSERT INTO answers VALUES 
(1,'a,b,a,c,d,b,x,x,y'),
(2,'b,b,a,c,d,d,x,x,y'),
(3, 'p,q,r,s,p,q')

I need to have result like below

id   a   b  c   d  e
1    2   2  1   1  0

what could be the best way?

columns are from a to z

Upvotes: 0

Views: 114

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

You need to count the specific letters on each row. This is possible:

select id,
       (len(ans) - len(replace(ans, 'a', ''))) as a,
       (len(ans) - len(replace(ans, 'b', ''))) as b,
       (len(ans) - len(replace(ans, 'c', ''))) as c,
       (len(ans) - len(replace(ans, 'd', ''))) as d,
       (len(ans) - len(replace(ans, 'e', ''))) as e
from answers;

The count is calculated by taking the length of the ans column and then subtracting the length when each letter is removed.

Upvotes: 2

Related Questions