How to do this in a report?

I have to write a report in SQL that takes the following data structure:
CREATE TABLE [dbo].[Sales_Customer_List] (
[cust_no] [char] (10) NOT NULL ,
[cust_name] [char] (35) NULL ,
[distr_channel] [char] (2) NOT NULL ,
[sold_to_sales_grp] [char] (3) NULL ,
[ship_to_sales_grp] [char] (3) NULL ,
[sold_to_sales_rep_cd] [char] (10) NULL ,
[ship_to_sales_rep_cd] [char] (10) NULL ,
[sold_to_sales_rep] [varchar] (30) NULL ,
[ship_to_sales_rep] [varchar] (30) NULL ,
[csr] [char] (35) NOT NULL ,
[csr_email] [char] (60) NOT NULL ,
[credit_mgr] [char] (35) NOT NULL ,
[sales_region] [char] (20) NULL ,
[BusArea01] [decimal](18, 2) NULL ,
[BusArea02] [decimal](18, 2) NULL ,
[BusArea03] [decimal](18, 2) NULL
) ON [PRIMARY]
--GO
and gives me a listing by customer number, customer name,
sold_to_sales_grp, etc thru the Sales region field.
The data itself can have multiple distr_channel values but the other
fields (excluding the busarea01, 02 and 03 fields) will not change
between customers.
The data would be like:
custno = 1234
custname= Customer1
distr_channel = DS
etc etc etc and the BusArea fields would be
BusArea01 = 0
BusArea02 = 137
BusArea03 = 984
A second record would be:
custno = 1234
custname= Customer1
distr_channel = GM
etc etc etc and the BusArea fields would be
BusArea01 = 855
BusArea02 = 0
BusArea03 = 211
A Third record would be:
custno = 6543
Custname = Customer2
distr_channel = CH
etc etc etc and the BusArea fields would be
BusArea01 = 1250
BusArea02 = 0
BusArea03 = 335
A fourth record would be
Custno = 8998
Custname = Customer3
distr_channel = DL
etc etc etc and the BusArea fields would be
BusArea01 = 25000
BusArea02 = 0
BusArea03 = 550
A fifth record would be
Custno = 8998
Custname = Customer3
distr_channel = WA
etc etc etc and the BusArea fields would be
BusArea01 = 0
BusArea02 = 15000
BusArea03 = 0
The line I need to have on my report would be:
C No C Name BA01 BA02 BA03
1234 Customer1 etc etc etc GM 855 DS 137 DS & GM 211 + 984
6543 Customer2 etc etc etc CH 1250 CH 0 CH 335
8998 Customer3 etc etc etc DL 25000 WA 15000 DL 550
Some customers would have 1 Distr_channel, some 4 or 5.
How would you do this in SQL?
Thanks,
SCBetter if you do this in the client app / reporting tool and not in sql serv
er.
HOW TO: Rotate a Table in SQL Server
http://support.microsoft.com/defaul...574&Product=sql
Dynamic Crosstab Queries
http://www.windowsitpro.com/SQLServ...5608/15608.html
Dynamic Cross-Tabs/Pivot Tables
http://www.sqlteam.com/item.asp?ItemID=2955
AMB
"Blasting Cap" wrote:

> I have to write a report in SQL that takes the following data structure:
> CREATE TABLE [dbo].[Sales_Customer_List] (
> [cust_no] [char] (10) NOT NULL ,
> [cust_name] [char] (35) NULL ,
> [distr_channel] [char] (2) NOT NULL ,
> [sold_to_sales_grp] [char] (3) NULL ,
> [ship_to_sales_grp] [char] (3) NULL ,
> [sold_to_sales_rep_cd] [char] (10) NULL ,
> [ship_to_sales_rep_cd] [char] (10) NULL ,
> [sold_to_sales_rep] [varchar] (30) NULL ,
> [ship_to_sales_rep] [varchar] (30) NULL ,
> [csr] [char] (35) NOT NULL ,
> [csr_email] [char] (60) NOT NULL ,
> [credit_mgr] [char] (35) NOT NULL ,
> [sales_region] [char] (20) NULL ,
> [BusArea01] [decimal](18, 2) NULL ,
> [BusArea02] [decimal](18, 2) NULL ,
> [BusArea03] [decimal](18, 2) NULL
> ) ON [PRIMARY]
> --GO
> and gives me a listing by customer number, customer name,
> sold_to_sales_grp, etc thru the Sales region field.
> The data itself can have multiple distr_channel values but the other
> fields (excluding the busarea01, 02 and 03 fields) will not change
> between customers.
> The data would be like:
> custno = 1234
> custname= Customer1
> distr_channel = DS
> etc etc etc and the BusArea fields would be
> BusArea01 = 0
> BusArea02 = 137
> BusArea03 = 984
> A second record would be:
> custno = 1234
> custname= Customer1
> distr_channel = GM
> etc etc etc and the BusArea fields would be
> BusArea01 = 855
> BusArea02 = 0
> BusArea03 = 211
> A Third record would be:
> custno = 6543
> Custname = Customer2
> distr_channel = CH
> etc etc etc and the BusArea fields would be
> BusArea01 = 1250
> BusArea02 = 0
> BusArea03 = 335
> A fourth record would be
> Custno = 8998
> Custname = Customer3
> distr_channel = DL
> etc etc etc and the BusArea fields would be
> BusArea01 = 25000
> BusArea02 = 0
> BusArea03 = 550
> A fifth record would be
> Custno = 8998
> Custname = Customer3
> distr_channel = WA
> etc etc etc and the BusArea fields would be
> BusArea01 = 0
> BusArea02 = 15000
> BusArea03 = 0
>
> The line I need to have on my report would be:
> C No C Name BA01 BA02 BA03
> 1234 Customer1 etc etc etc GM 855 DS 137 DS & GM 211 + 984
> 6543 Customer2 etc etc etc CH 1250 CH 0 CH 335
> 8998 Customer3 etc etc etc DL 25000 WA 15000 DL 550
> Some customers would have 1 Distr_channel, some 4 or 5.
> How would you do this in SQL?
>
> Thanks,
> SC
>