• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

SQL Question

Say I have a table like this

Pcode      Val1      Val2
A             1           2
B             1           NULL
C             9           14

What SQL would I need to turn it into a single column table like this?

A1
A2
B1
C9
C14

0
Fulgent
Asked:
Fulgent
  • 6
1 Solution
 
HainKurtSr. System AnalystCommented:
try this:

select a+val1 col from mytable
union
select a+val2 col from myTable

assuming they all are varchar
0
 
HainKurtSr. System AnalystCommented:
if not

select cast(a as varchar)+val1 col from mytable
union
select cast(a as varchar)+val2 col from myTable

0
 
HainKurtSr. System AnalystCommented:
what do you want to do if val1 and val2 is same for row 1?
if you want 2 values then use union all

select cast(a as varchar)+val1 col from mytable
union all
select cast(a as varchar)+val2 col from myTable

0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
HainKurtSr. System AnalystCommented:
oops, you want to ignore nulls

select cast(a as varchar)+val1 col from mytable where val1 is not null
union all
select cast(a as varchar)+val2 col from myTable where val2 is not null

0
 
Lee SavidgeCommented:
try:

select pcode + cast(val1 as nvarchar(10)) as mynewcolumn from mytesttable where val1 is not null
union
select pcode + cast(val2 as nvarchar(10)) as mynewcolumn from mytesttable where val2 is not null
0
 
FulgentAuthor Commented:

Thanks for the replies, I forgot to mention that the number of columns is dynamic, can be anything up to 99 columns
0
 
HainKurtSr. System AnalystCommented:
up to 99 columns? give a sample for more than 2 columns...

something like this?

Pcode      Val1      Val2       Val3 ... Val99
A             1           2            NULL... 3
B             1           NULL      5......... 6
C             9           14          NULL... 1
0
 
HainKurtSr. System AnalystCommented:
create view v_myTable as
select pcode + cast(val1 as nvarchar(10)) as newcolumn from mytesttable where val1 is not null
union all
select pcode + cast(val2 as nvarchar(10)) from mytesttable where val2 is not null
union all
...
union all
select pcode + cast(val99 as nvarchar(10)) from mytesttable where val99 is not null

then use

select * from v_myTable
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now