?
Solved

SQL Columns

Posted on 2010-01-09
8
Medium Priority
?
205 Views
Last Modified: 2012-05-08
I want to convert this back to one column by concatenating the variables i.e.   (@a1 " & " @a2) can this be done?


DECLARE @A1 VARCHAR(2),
            @A2 VARCHAR(2),
            @A3 VARCHAR(2),
            @A4 VARCHAR(2),
            @A5 VARCHAR(2),
            @A6 VARCHAR(2)



SELECT SUBSTRING (LottoNum,1, 2) AS A1, SUBSTRING (LottoNum,3, 2) AS A2,SUBSTRING (LottoNum,5, 2) as A3,SUBSTRING (LottoNum,7, 2) AS A4,
SUBSTRING (LottoNum,9, 2) AS A5, SUBSTRING (LottoNum,11, 2) AS A6
     
FROM [Lotto].[dbo].[LottoAccount]
0
Comment
Question by:Dell12805
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
8 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26274909
your code does not fill @a1 etc ...

SELECT @a1 = SUBSTRING (LottoNum,1, 2) AS A1,  etc ...

as from then, you can do :
set @result = @a1 + @a2 ... etc ..
0
 
LVL 56

Expert Comment

by:Bill Prew
ID: 26275015
In this particular example you could of course do this as well.

SELECT @result = SUBSTRING(LottoNum,1, 12) FROM [Lotto].[dbo].[LottoAccount]

That may not work if you to take the data from the vars rather than the table, but since the pieces were contiguous in the original field this could be an alternate approach.

~bp
0
 

Author Comment

by:Dell12805
ID: 26275082
This did not work.

DECLARE @A1 VARCHAR(2),
        @A2 VARCHAR(2),
            @result VARCHAR(20)

SET @result = @A1 + @A2

SELECT @result = (SUBSTRING (LottoNum,1, 2), SUBSTRING (LottoNum,3, 2))
from dbo.LottoAccount

print @result
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 

Author Comment

by:Dell12805
ID: 26275104
I tried this as well Bill

DECLARE @A1 VARCHAR(2),
        @A2 VARCHAR(2),
            @result VARCHAR(20)


SELECT @result = SUBSTRING (LottoNum,1, 2) AS A1, SUBSTRING (LottoNum,3, 2) AS A2
SET @result = @A1 + @A2

from dbo.LottoAccount

print @result
0
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 26275121
your code does not make much sense so far.
you set @restult, but overwrite it then again ...
0
 
LVL 56

Accepted Solution

by:
Bill Prew earned 2000 total points
ID: 26275134
Either of these should work:

DECLARE @A1 VARCHAR(2),
        @A2 VARCHAR(2),
        @result VARCHAR(20)
SELECT @a1 = SUBSTRING (LottoNum,1, 2) FROM [Lotto].[dbo].[LottoAccount]
SELECT @a2 = SUBSTRING (LottoNum,3, 2) FROM [Lotto].[dbo].[LottoAccount]
set @result = @a1 + @a2
print @result

DECLARE @result VARCHAR(20)
SELECT @result = SUBSTRING(LottoNum,1, 2) + SUBSTRING(LottoNum,3, 2) FROM [Lotto].[dbo].[LottoAccount]
print @result

~bp
0
 

Author Comment

by:Dell12805
ID: 26275136
This is why I am asking for help :


SELECT @result = SUBSTRING (LottoNum,1, 2) AS A1, SUBSTRING (LottoNum,3, 2) AS A2


from dbo.LottoAccount

print @result
0
 

Author Closing Comment

by:Dell12805
ID: 31675040
Thanks for your help
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
The viewer will learn the benefit of using external CSS files and the relationship between class and ID selectors. Create your external css file by saving it as style.css then set up your style tags: (CODE) Reference the nav tag and set your prop…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question