• 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
[Webinar] Kill tickets & tabs using PowerShell

Are you tired of cycling through the same browser tabs everyday to close the same repetitive tickets? In this webinar JumpCloud will show how you can leverage RESTful APIs to build your own PowerShell modules to kill tickets & tabs using the PowerShell command Invoke-RestMethod.

 
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

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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