June 28, 2013 at 2:52 pm
Hi,
I have 3 tables in the first table customernumber i have data filled. it has 4 columns which specifies the total number of different kinds of customers in the market.The second table has two columns customertypeID and customers type so it has only 4 rows (every customerType is assigned with a customerTypeID). here comes my work there is a third table which has CustomertypeID and number of customers. the data in the third table is filled basing on the first and second tables. the columns in the first table should match with the rows in the second table and data has to come into third table can any one of you help me in this ?
Create table Customersnumber
(
cityname varchar(25)
,Loyal Customers interger
,DiscountCustomers integer
,ImpulseCustomers integer
,NeedBasedCustomers integer
,WanderingCustomers integer
)
insert into customersnumber
values(1,5,8,56);
insert into customersnumber
values(7,65,98,756);
create table customertype
(
customertypeID int identity(1,1)
, Customertype
)
insert into customertype
values('DiscountCustomers');
insert into customertype
values('ImpulseCustomers');
insert into customertype
values('NeedbasedCustomers');
insert into customertype
values('WanderingCustomers')
create table customers
(
ID int identity(1,1)
, customertypeID int
,NumberOfCustomers int
)
June 28, 2013 at 3:21 pm
Thank you for posting ddl and sample data. Unfortunately your script doesn't work. There are a few syntax error, once I fixed those the inserts don't work because the number of columns don't match. Please correct the sample data, test it, and post it again so we can help.
Even if the data were ok I can't understand what your requirement is based on your description. Are you trying to create rows in the Customer table based on the data in the other two tables? Can you explain the logic here and post what you expect as desired output based on the sample data?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
June 28, 2013 at 3:27 pm
yeah i strugged thru the broken DDL as well;
unfortunately, becuase you have no true relationship to the two tables, you have to make assumptions to get teh data to work the way you want.
instead of a table custmers, a VIEW woudl be better, since the table woudl be out of date as soon as new rows get added to any of your tables.
here's an ugly implementation that i really hate, but that does what you ask, kinda, sorta, maybe...
Create table Customersnumber
(
cityname varchar(25)
,LoyalCustomers int
,DiscountCustomers int
,ImpulseCustomers int
,NeedBasedCustomers int
,WanderingCustomers int
)
insert into customersnumber
values('Miami',1,5,8,56,0);
insert into customersnumber
values('Fort Lauderdale',7,65,98,756,0);
create table customertype
(
customertypeID int identity(1,1)
, Customertype varchar(50)
)
insert into customertype
values('LoyalCustomers');
insert into customertype
values('DiscountCustomers');
insert into customertype
values('ImpulseCustomers');
insert into customertype
values('NeedbasedCustomers');
insert into customertype
values('WanderingCustomers')
GO
CREATE VIEW customers
AS
SELECT customertype.customertypeID,customertype.Customertype, SUM(LoyalCustomers) As TotalCustomers FROM Customersnumber INNER JOIN customertype ON customertype.Customertype= 'LoyalCustomers' GROUP BY customertype.customertypeID,customertype.Customertype UNION ALL
SELECT customertype.customertypeID,customertype.Customertype, SUM(DiscountCustomers) As TotalCustomers FROM Customersnumber INNER JOIN customertype ON customertype.Customertype= 'DiscountCustomers' GROUP BY customertype.customertypeID,customertype.Customertype UNION ALL
SELECT customertype.customertypeID,customertype.Customertype, SUM(ImpulseCustomers) As TotalCustomers FROM Customersnumber INNER JOIN customertype ON customertype.Customertype= 'ImpulseCustomers' GROUP BY customertype.customertypeID,customertype.Customertype UNION ALL
SELECT customertype.customertypeID,customertype.Customertype, SUM(NeedbasedCustomers) As TotalCustomers FROM Customersnumber INNER JOIN customertype ON customertype.Customertype= 'NeedbasedCustomers' GROUP BY customertype.customertypeID,customertype.Customertype UNION ALL
SELECT customertype.customertypeID,customertype.Customertype, SUM(WanderingCustomers) As TotalCustomers FROM Customersnumber INNER JOIN customertype ON customertype.Customertype= 'WanderingCustomers' GROUP BY customertype.customertypeID,customertype.Customertype
GO
SELECT * FROM customers
Lowell
June 28, 2013 at 4:31 pm
Thanks guys for your reply. I am sorry for the broken DDL
Here is the correct Table structure ans some sample Data
Create table Customersnumber
(
cityname varchar(25)
,LoyalCustomers int
,DiscountCustomers int
,ImpulseCustomers int
,NeedBasedCustomers int
,WanderingCustomers int
)
insert into customersnumber
values('Miami',1,5,8,56,0);
insert into customersnumber
values('Fort Lauderdale',7,65,98,756,0);
create table customertype
(
customertypeID int identity(1,1)
, Customertype varchar(50)
)
insert into customertype
values('LoyalCustomers');
insert into customertype
values('DiscountCustomers');
insert into customertype
values('ImpulseCustomers');
insert into customertype
values('NeedbasedCustomers');
insert into customertype
values('WanderingCustomers')
Create table customers
(
ID int identity(1,1)
, customertypeID int
,NumberOfCustomers int
)
select * from customertype
CustomerType ID CustomerType
1 LoyalCustomers
2 DiscountCustomers
3 ImpulseCustomers
4 NeedbasedCustomers
5 WanderingCustomers
select * from Customersnumber
cityname LoyalCustomers DiscountCustomers ImpulseCustomers NeedBasedCustomers WanderingCustomers
Miami 1 5 8 56 0
Fort Lauderdale 7 65 98 756 0
select * from customers
ID CustomerTypeID NumberofCustomers
This is how the tables look, In the third table i want data like this
select * from customers
ID CustomerTypeID NumberofCustomers
1 1 1
2 1 7
3 2 5
4 2 65
5 3 8
6 3 98
7 4 56
8 4 756
9 5 0
10 5 0
Please let me know if i was wrong any where. Again Thanks for your time
June 29, 2013 at 5:09 am
try this
with pvt
as
(
select customertypepvt,sumpvt
from dbo.Customersnumber
unpivot
(
sumpvt for customertypepvt in([LoyalCustomers]
,[DiscountCustomers]
,[ImpulseCustomers]
,[NeedBasedCustomers]
,[WanderingCustomers])
)as expl
)
insert into dbo.customerstu
select customertypepvt,customertypeID,sum(sumpvt)
from pvt table1
join dbo.customertype table2
on(table2.Customertype=table1.customertypepvt)
group by customertypepvt,customertypeID
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply