October 31, 2007 at 12:26 pm
Can anybody tell me how I can get this data into one row? Each account can have any number of Small, medium and Large lights. Each row in the table represents 1 light per one account. I can get it so that I have up to three records per account, one per each light type, but I can't think of how to get 1 record per account with the counts of each small, medium and large lights as columns (i.e. Account, SmallLights, MediumLights, LargeLights).
here is my current SQL, any help would be much appreciated...
SELECT ACCT, Count(DVC_TYPE) as NumberOfLights, Substr(DVC_TYPE,1,2) as LightSize FROM DVC_SRV WHERE Substr(DVC_TYPE,1,2) IN ('SM', 'MD', 'LR') GROUP BY ACCT, Substr(DVC_TYPE,1,2)
October 31, 2007 at 1:44 pm
November 1, 2007 at 12:14 pm
...or
Have a look at Cross-tab reports in BOL.Go to the search tab and search for
Pivot and you should see an entry.
November 1, 2007 at 2:38 pm
SELECT ACCT,
sum(case when Substr(DVC_TYPE,1,2)='SM' then 1 else 0 end) as smallcount,
sum(case when Substr(DVC_TYPE,1,2)='MD' then 1 else 0 end) as mediumcount,
sum(case when Substr(DVC_TYPE,1,2)='LG' then 1 else 0 end) as largecount,
FROM DVC_SRV
WHERE Substr(DVC_TYPE,1,2) IN ('SM', 'MD', 'LR')
GROUP BY ACCT
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply