February 4, 2009 at 1:13 pm
Hello experts,
How can I add all rows into one new column?
SELECT DDS, COUNT(DEClearedDate) AS DECleared, COUNT(SomaticMCClearedDate) AS SomaticMCCleared, COUNT(PsycMCClearedDate) AS PsycMCCleared, COUNT(DESecondClearedDate) AS DESecondCleared, COUNT(SomaticMCSecondClearedDate) AS SomaticMCSecondCleared,
COUNT(PsycMCSecondClearedDate) AS PsycMCSecondCleared, COUNT(DEThirdClearedDate) AS DEThirdCleared, COUNT(SomaticMCThirdClearedDate) AS SomaticMCThirdCleared, COUNT(PsycMCThirdClearedDate) AS PsycMCThirdCleared, COUNT(DEFourthClearedDate) AS DEFourthCleared, COUNT(SomaticMCFourthClearedDate) AS SomaticMCFourthCleared,
COUNT(PsycMCFourthClearedDate) AS PsycMCFourthCleared, CONVERT(varchar(16), DEClearedDate, 101) AS DEClearedDate, CONVERT(varchar(16), SomaticMCClearedDate, 101) AS SomaticMCClearedDate, CONVERT(varchar(16), PsycMCClearedDate, 101) AS PsycMCClearedDate, CONVERT(varchar(16), DESecondClearedDate, 101) AS DESecondClearedDate, CONVERT(varchar(16), SomaticMCSecondClearedDate, 101) AS SomaticMCSecondClearedDate, CONVERT(varchar(16), PsycMCSecondClearedDate, 101) AS PsycMCSecondClearedDate, CONVERT(varchar(16), DEThirdClearedDate, 101) AS DEThirdClearedDate, CONVERT(varchar(16), SomaticMCThirdClearedDate, 101) AS SomaticMCThirdClearedDate, CONVERT(varchar(16), PsycMCThirdClearedDate, 101) AS PsycMCThirdClearedDate, CONVERT(varchar(16), DEFourthClearedDate, 101) AS DEFourthClearedDate, CONVERT(varchar(16), SomaticMCFourthClearedDate, 101) AS SomaticMCFourthClearedDate, CONVERT(varchar(16), PsycMCFourthClearedDate, 101) AS PsycMCFourthClearedDate
FROM dbo.ROCAPData
WHERE (DDS = 'BO')
GROUP BY DEClearedDate, SomaticMCClearedDate, PsycMCClearedDate, DESecondClearedDate, SomaticMCSecondClearedDate, PsycMCSecondClearedDate,
DEThirdClearedDate, SomaticMCThirdClearedDate, PsycMCThirdClearedDate, DEFourthClearedDate, SomaticMCFourthClearedDate, PsycMCFourthClearedDate, DDS
Insert INTO dbo.ROCAPData (DDS, DEClearedDate, SomaticMCClearedDate, PsycMCClearedDate, DESecondClearedDate, SomaticMCSecondClearedDate, PsycMCSecondClearedDate, DEThirdClearedDate, SomaticMCThirdClearedDate, PsycMCThirdClearedDate, DEFourthClearedDate, SomaticMCFourthClearedDate, PsycMCFourthClearedDate)
Select 'BO', '1/1/2008 12:00AM', '1/2/2009 12:00AM', '1/2/2009 12:00AM', '1/3/2009 12:00AM' , '1/3/2009 12:00AM', '1/4/2009 12:00AM', '1/5/2009 12:00AM', '1/5/2009 12:00AM',
'1/12/2009 12:00AM', '1/12/2009 12:00AM', '1/14/2009 12:00AM', '1/14/2009 12:00AM'
If there is a date in a cell then count as 1 record.
now, I wish to add all columns (DECleared, SomaticMCCleared, PsycMCCleared, DESecondCleared, .....PsycMCFourthCleared) into one column and calls it as BOCLEARED because all records are belonged to "BO" office.
And Wish add all columns date (DEClearedDate, SomaticMCClearedDate, ......PsycMCFourthClearedDate) AS BOCLEAREDDATE
Here is the outlook that I wish it to display
DDS BOCLEARED BOCLEAREDDATE
BO 1 1/1/2009
BO 2 1/2/2009
BO 2 1/3/2009
BO 1 1/4/2009
BO 2 1/5/2009
BO 2 1/12/2009
BO 2 1/14/2009
Thank you
February 4, 2009 at 1:30 pm
I'm not sure where your BOCLEARED column gets it's data. May be in there somewhere, but that table structure is close to unreadable.
What you can do with a situation like this is use a Union All query. Would look something like this:
select DDS, DEClearedDate
from dbo.ROCAPData
where DEClearedDate is not null
Union All
select DDS, SomaticMCClearedDate
from dbo.ROCAPData
where SomaticMCClearedDate is not null
And so on, through all your cleared date columns, one of top of the other.
I'd seriously look into normalizing this table by having something like DDS, ClearedDate, ClearedType as the only columns, instead of all those columns for the various types of clearing.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply