How to split character values in each row and create new columns in SQL

Question

I have a field that consists of different strings of letters in each row:

BAAAAZZAFBF
AAAAAZZAFBA
FAZZZAA

I would like to output these rows into columns based on each character (one column would be B, the next would be A, etc.).

I have this query right now but only transposes rows to columns - does not separate them.

SELECT Primary Key,
       MAX(CASE WHEN DNB_MATCH_GRADE = 'B__________' THEN 1 END) B_Name,
       MAX(CASE WHEN DNB_MATCH_GRADE = 'A__________' THEN 2 END) A_Name,
       MAX(CASE WHEN DNB_MATCH_GRADE = 'F_______' THEN 3 END) F_name
  FROM TABLE
 GROUP BY Primary key

Output table would look like this with each section corresponding to a field in table:

Primary Key | A | B | D | Z | F

Please show the results that you want.

Gordon Linoff
– Gordon Linoff

2020-07-22 21:30:19 +00:00
Commented Jul 22, 2020 at 21:30 — Gordon Linoff
– Gordon Linoff, Commented Jul 22, 2020 at 21:30

Gordon Linoff · Accepted Answer · 2020-07-22 21:31:15Z

1

Is this what you want?

SELECT Primary Key,
       MAX(CASE WHEN DNB_MATCH_GRADE = 'B__________' THEN DNB_MATCH_GRADE END) as B_Name,
       MAX(CASE WHEN DNB_MATCH_GRADE = 'A__________' THEN DNB_MATCH_GRADE END) as A_Name,
       MAX(CASE WHEN DNB_MATCH_GRADE = 'F_______' THEN DNB_MATCH_GRADE END) as F_name
FROM TABLE
GROUP BY Primary key

answered Jul 22, 2020 at 21:31

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to split character values in each row and create new columns in SQL

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related