Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mitasid
Yak Posting Veteran
51 Posts |
 Posted - 2006-06-03 : 21:32:17
|
| I have got two tablestable 1 has data for year 2000 as followsCompany ID Units_jan Units_feb............Units_DecABX 12 3 5 .............45 34 5 454 354354................table 2 has data for year 2001 as followsCompany ID Units_jan Units_feb............Units_DecAwX 12 3 5 .............45 34 5 454 354354................what i want is something like thisCompany ID Units_jan Units_feb Units_Dec Units_jan units febABC 23 ABX 12 3 5 .............45 AwX 12 how do i do this??? |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-03 : 21:37:13
|
| select CompanyID = coalesce(t1.CompanyID, t2.CompanyID), jan2000 = t1.Units_jan, feb2000 = t1.Units_feb, ...jan2001 = t2.Units_jan, feb2001 = t2.Units_feb, ...from table1 t1full outer join table 2 t2on t1.CompanyID = t2.CompanyID==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-06-05 : 08:05:46
|
mitasid - Your table structure is not the recommended way. This will very likely mean that development and maintenance will cost you (or your company) a lot more money than it could, and I'd therefore recommend you seriously consider changing your table structure...http://www.datamodel.org/NormalizationRules.htmlIn your case you could have one table with 4 columns...CompanyID Year Month UnitsABX 2000 1 12ABX 2000 2 3AwX 2001 1 ... Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
mitasid
Yak Posting Veteran
51 Posts |
Posted - 2006-06-08 : 23:42:33
|
| Hi RyanMy problem is i want to have a report which should be of the following formatComp ID Year Vol_jan vol_feb ....The requirement is like this only..and the only reason for the query is to develop a report |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-06-09 : 06:08:16
|
quote:
Originally posted by mitasid
Hi RyanMy problem is i want to have a report which should be of the following formatComp ID Year Vol_jan vol_feb ....The requirement is like this only..and the only reason for the query is to develop a report
Yes, I understand all that mitasid. My point was that you might have another, more serious, problem. Your table structure is very poor, and that will likely cost you time and money.If I were you, I'd change my table structure and then solve the requested problem based on those more solid foundations.Ryan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
mitasid
Yak Posting Veteran
51 Posts |
Posted - 2006-06-09 : 06:23:44
|
| well i think you are right rian!But i need ur help for this ..i have got my tables stored in this format only..so how shall i convert it into the format u suggested as in how do i convert rows into columns?? |
|
|
|
RyanRandall
Master Smack Fu Yak Hacker
1074 Posts |
Posted - 2006-06-09 : 07:04:49
|
quote:
well i think you are right rian!
Oh how I love those words!  Something like this...--old structuredeclare @table1 table (CompanyID varchar(10), Units_jan int, Units_feb int, Units_Dec int)insert @table1 select 'ABX', 12, 3, 45union all select 'XYZ', 34, 5, 354354declare @table2 table (CompanyID varchar(10), Units_jan int, Units_feb int, Units_Dec int)insert @table2 select 'AwX', 12, 3, 45union all select 'XYZ', 34, 5, 354354--new structuredeclare @t table (CompanyID varchar(10), Year int, Month int, Units int)insert @t select CompanyID, 2000, 1, Units_jan from @table1union all select CompanyID, 2000, 2, Units_feb from @table1union all select CompanyID, 2000, 12, Units_Dec from @table1union all select CompanyID, 2001, 1, Units_jan from @table2union all select CompanyID, 2001, 2, Units_feb from @table2union all select CompanyID, 2001, 12, Units_Dec from @table2select * from @t--pivot/crosstab calculationselect CompanyID, sum(case when year * 100 + month = 200001 then units else 0 end) as Units_jan, sum(case when year * 100 + month = 200002 then units else 0 end) as Units_feb, sum(case when year * 100 + month = 200012 then units else 0 end) as Units_Dec, sum(case when year * 100 + month = 200101 then units else 0 end) as Units_jan, sum(case when year * 100 + month = 200102 then units else 0 end) as Units_feb, sum(case when year * 100 + month = 200112 then units else 0 end) as Units_Decfrom @tgroup by CompanyID Or, for the pivot, you can do it at the front end, or you can use a stored procedure to do it for you (probably not necessary in this case because your columns are static)...http://www.sqlteam.com/item.asp?ItemID=2955http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspxRyan Randallwww.monsoonmalabar.com London-based IT consultancy Solutions are easy. Understanding the problem, now, that's the hard part. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2006-06-09 : 07:11:27
|
| Did you see the post I made earlier just after your original question?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
mitasid
Yak Posting Veteran
51 Posts |
Posted - 2006-06-10 : 04:03:47
|
| hi ryan thanks a tonne for ur help..i am sure this structure will certainly work...my question to u is will it reduce the processing time of the query as i dont have any indexes on it and each table has got about a million records |
|
|
|
mitasid
Yak Posting Veteran
51 Posts |
Posted - 2006-06-11 : 20:20:25
|
| hi ryan thanks a tonne for ur help..i am sure this structure will certainly work...my question to u is will it reduce the processing time of the query as i dont have any indexes on it and each table has got about a million records |
|
|
|
|
|
|
|
|
append query