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 |
|
sbt1
Yak Posting Veteran
89 Posts |
 Posted - 2005-01-27 : 07:12:39
|
| I have data in a table that I'm trying to get a summary of. It's designed to simply show whether or not there is anything scheduled for each customer in each of the 12 months of the year.The records have fields for a Customer name, plus each month of the year (which contain either 0 or some integer). There may be multiple records for each Customer.The data looks like this:Customer Jan Feb Mar Apr ...Alpha 0 2 1 0Beta 3 0 3 2Beta 0 1 1 0Beta 0 0 3 2Charlie 1 1 0 1What I'm trying to do is get the query to return this ('x's or some symbol to indicate that there is something > 0 for a customer in each month):Alpha x xBeta x x x xCharlie x x xHere's the query I have so far:SELECT DISTINCTCustomer,CASE Jan WHEN '' THEN '' ELSE '*' END,CASE Feb WHEN '' THEN '' ELSE '*' END,CASE Mar WHEN '' THEN '' ELSE '*' END,CASE Apr WHEN '' THEN '' ELSE '*' END,CASE May WHEN '' THEN '' ELSE '*' END,CASE Jun WHEN '' THEN '' ELSE '*' END,CASE Jul WHEN '' THEN '' ELSE '*' END,CASE Aug WHEN '' THEN '' ELSE '*' END,CASE Sep WHEN '' THEN '' ELSE '*' END,CASE Oct WHEN '' THEN '' ELSE '*' END,CASE Nov WHEN '' THEN '' ELSE '*' END,CASE Dec WHEN '' THEN '' ELSE '*' ENDFROM ProfileDBbut what it's doing is returning a separate line for each corresponding line in the data (using the example above, it would give me 3 rows for Beta.How can I fix this? Thx in advance for any help. |
|
|
JimL
SQL Slinging Yak Ranger
1537 Posts |
Posted - 2005-01-27 : 07:34:07
|
| Select Custname,Jan1 = case when Sum(jan) > 0 then Then 'X' Else Cast(0 as varchar(1))endfeb1 = case when Sum(feb) > 0 then Then 'X' Else Cast(0 as varchar(1))endmar1 = ..............................from yourtablegroup by CustnameJimUsers <> Logic |
 |
|
|
|
|
|
|
|
Need query help