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

Combine 3 Queries into One

Hello,
I have a large database that contains info about several types of business
industries. There is a table called Co_Ind_Sales which contains industry
sales that I need to sum up for 3 separate industries. There is an
Industry_Id in the Co_Ind_Sales table. I am using the following SQL in
Query Analyzer and I get the right result for the 1 industry I am using:
select SUM(cis.Sales)as Sales Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id =9
And c.listing_type in ('H','S')
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales)
,1)),1,100))
AS 'Total Foodservice Revenues - Chain Restaurants'
From Lisa_TotalRev
This gives me the following results:
Total Foodservice Revenues - Chain Restaurants
---
$177,835,953,607.00
I now have 2 other industries that I need to do the same thing with, they
would be:
And cis.industry_id =42
And cis.industry_id =52
I need to combine all three result sets into one report, like such:
Total Foodservice Revenues
---
$177,835,953,607.00 Chain Restaurants
Total Foodservice Revenues
---
$16,077,196,215.00 Hotel/Motel
Total Foodservice Revenues
---
$30,244,812,996.00 Foodservice Management Operators
Please help. I am rather new to SQL so if you could add to my code the
pieces that I need that would be wonderful. I have trouble understanding
the help file. I do better with examples not just text. Thanks for any help
anyone can give.On Thu, 12 May 2005 18:34:56 GMT, "Lisa Farina via SQLMonster.com"
<forum@.nospam.SQLMonster.com> wrote:
Something like:
>select
> SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
>Into Lisa_TotalRev
>From company_industry ci, co_ind_sales cis, company c
>Where ci.company_id=cis.company_id
> And cis.company_id=c.company_id
> And cis.current_record='Y'
> And ci.in_book='Y'
> And cis.industry_id in (9, 42, 43)
> And c.listing_type in ('H','S')
>GROUP BY cis.industry_id
>SELECT
> '$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
> AS 'Total Foodservice Revenues',
> IndustryName
>From Lisa_TotalRev
and it would make me personally very happy if you learned to use the
newer ANSI join style!
Welcome to SQL!
Josh|||Sorry I am using the older style. I will do my best to learn the newer way.
I'm not sure if you actually posted a solution because the messgage started
with Something like: [quoted text clipped - 17 lines]
and then it was cut off. Could you please repost. Thanks.|||On Thu, 12 May 2005 19:33:46 GMT, "Lisa Farina via SQLMonster.com"
<forum@.SQLMonster.com> wrote:
>Sorry I am using the older style. I will do my best to learn the newer way.
>I'm not sure if you actually posted a solution because the messgage started
>with Something like: [quoted text clipped - 17 lines]
>and then it was cut off. Could you please repost. Thanks.
I think that's a display option you can turn off, but here's the
pseudo-code I posted with the quotes removed.
J.
select
SUM(cis.Sales)as Sales, MAX(ci.IndustryName) as IndustryName
Into Lisa_TotalRev
From company_industry ci, co_ind_sales cis, company c
Where ci.company_id=cis.company_id
And cis.company_id=c.company_id
And cis.current_record='Y'
And ci.in_book='Y'
And cis.industry_id in (9, 42, 43)
And c.listing_type in ('H','S')
GROUP BY cis.industry_id
SELECT
'$'+REVERSE(SUBSTRING(REVERSE(CONVERT(VARCHAR(100),CONVERT(MONEY,Sales),1)),1,100))
AS 'Total Foodservice Revenues',
IndustryName
From Lisa_TotalRev

columns in select list

In your client, sure -- every client library can do that. In SQL Server, probably not. What is the business or technical problem you're trying to solve? Perhaps we can come up with a better way of handling it. -- Adam MachanicSQL Server MVPhttp://www.datamanipulation.net-- <buragohain@.discussions.microsoft.com> wrote in message news:6926c563-86b6-40a5-a3f0-3bc32f8e9e94@.discussions.microsoft.com... hi friends!Suppose, i have a query:SELECT fname AS Firstname, lname AS Lastname FROM CustomerNow is it possible to get the column names produced by the above query? LikeFirstnameLastnamejust like we extract column names from information_schema.columns for a table name?with regards,Buragohain

hi friends!

Suppose, i have a query:

SELECT fname AS Firstname, lname AS Lastname FROM Customer

Now is it possible to get the column names produced by the above query? Like

Firstname
Lastname

just like we extract column names from information_schema.columns for a table name?

with regards,
Buragohain

|||What are you consuming it with? ADO with vb? ado.net ?
|||Hi friends!
Thanx everyone for your response.

By creating a temporary table using the query, then accessing the columns, and then deleting the temporary table, will solve the problem. But i wonder is there any in-built technique is Sql server for that or not. Like:

exec sp_columns 'Customer'

exec sp_columns 'select * from Customer' (wrong)

exec sp_columns 'SELECT fname AS Firstname, lname AS Lastname FROM Customer' (wrong)

Again, if there is any technique in ADO.NET, then it will be helpfull too.

(my problem was to show friendly column names to end user from several tables joined.)

with regards,