Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:
> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to put
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.

Combining data from multiple columns or views

I am trying to combine/report on data that is in one table. Due to system
limitations I have 3 columns that store the same type of business data.
Salesperson A, B and C are separate columns but need to be combined for
reporting. Can I create another table where I can use a select into to put
Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
into Column 1? If so, how?
Here is a sample data line:
Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
SalesAmount
I thought I could combine it into the following layout:
Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.Hi Dan,
Do you want to concatenate Salespersons A, B, and C all into one column, or
do you want to create a new row for Salesperson A, Salesperson B, and
salesperson C?
To concatenate, you can do the following:
select SalespersonA+SalespersonB+salespersonC, CommissionRate, DocNumber,
SalesAmount from tbl_name
If you want to create a new table and then insert into it from the other
table where each salesperson has their own row, then it would look something
like this:
create the table with columns for Salesperson, CommissionRate, DocNumber,
SalesAmount
Insert into NewTable
select SalespersonA, commissionRate, DocNumber, SalesAmount
Insert into NewTable
select SalespersonB, CommissionRate, DocNumber, SalesAmount
ect.
"Dan Shepherd" wrote:

> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to pu
t
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Thanks for the help... I tried the following syntax and it was not working:
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS, THRDPRTYNAME,
COMMRATE, REFERRATE) VALUES (SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW)
"Dan Shepherd" wrote:

> I am trying to combine/report on data that is in one table. Due to system
> limitations I have 3 columns that store the same type of business data.
> Salesperson A, B and C are separate columns but need to be combined for
> reporting. Can I create another table where I can use a select into to pu
t
> Salesperson A into Column 1, Salesperson B into Column 1 and Salesperson C
> into Column 1? If so, how?
> Here is a sample data line:
> Salesperson, Salesperson2, Salesperson3, CommissionRate, DocNumber,
> SalesAmount
> I thought I could combine it into the following layout:
> Salesperson, CommissionRate, DocNumber, SalesAmount but not sure how.|||Change it to
INSERT INTO XCOMMISSIONS (DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME,
COMMRATE, REFERRATE) SELECT DOCNUMBER, SLSPERSON, ACCTSTATUS,
THRDPRTYNAME, COMMRATE, REFERRATE FROM X_SLSPERSONA_VIEW
Regards
Amish Shah|||you can also do it with unions and renaming the columns.sqlsql

Collation problem

Hi
I need to correct some data in my SQL database due to a collation error
when I did the DTS from Paradox files to my SQL database I was using the "(Server default)" collation and now the extracted data has some weird chars
I need to use a Portuguese (Brazilian) collation, can i fix the error without loosing my curent data? If yes, how can I do it
Thanks!Marcus,
You can use ALTER TABLE xxx ALTER COLUMN yyy COLLATE collation_name
You should read the COLLATE topic. Whether you get what you want will
depend on whether some characters were remapped during the load into the
(server default) collation. (I don't think so.) You will have to drop
any indexes, statistics, etc. that you created on the columns.
Russell Fields
http://www.sqlpass.org/
2004 PASS Community Summit - Orlando
- The largest user-event dedicated to SQL Server!
"Marcus" <anonymous@.discussions.microsoft.com> wrote in message
news:DD900B9F-5D44-4ACE-9DAC-139EEC85591A@.microsoft.com...
> Hi!
> I need to correct some data in my SQL database due to a collation error!
> when I did the DTS from Paradox files to my SQL database I was using the
"(Server default)" collation and now the extracted data has some weird
chars!
> I need to use a Portuguese (Brazilian) collation, can i fix the error
without loosing my curent data? If yes, how can I do it?
>
> Thanks!