Sql Query - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Sql Query

Author

Topic

surendra1
Starting Member

2 Posts

Posted - 2006-12-11 : 23:18:20

Hi Everybody,

i need help from ur side, im not familiar with sql server development bez of im asking all, please provide me a simple sql server statement for below data.

I have following table:

CODE NAME INSTALLSTATUS

1 Raju UNIFY=OK, WINDOWS=NOTOK, LINUX=NOTOK, ORACLE=OK, BASIC=OK, DELPHI=OK, JAVA=NOTOK

2 Ashish UNIFY=NOTOK, WINDOWS=OK, LINUX=NOTOK, ORACLE=NOTOK, BASIC=NOTOK, DELPHI=OK, JAVA=NOTOK

3 Sharad UNIFY=OK, WINDOWS=OK, LINUX=OK, ORACLE=OK, BASIC=OK, DELPHI=NOTOK, JAVA=OK

… … …

… … …

Select …… from table

It should display:

CODE NAME TOTALOK TOTALNOTOK

1 Raju 4 3

2 Ashish 2 5

3 Sharad 6 1

… … … …

… … … …

Thanks
Surendra

prowla2006
Starting Member

3 Posts

Posted - 2006-12-11 : 23:36:56

I think you need to give a bit more detail on your table definition to get an answer....

does your table have 3 columns? Code, Name, InstallStatus

where an example InstallStatus = "UNIFY=OK, WINDOWS=NOTOK, LINUX=NOTOK, ORACLE=OK, BASIC=OK, DELPHI=OK, JAVA=NOTOK"

???

Or is there a column for each of the following UNIFY,WINDOWS,LINUX,ORACLE,BASIC,DELPHI,JAVA

???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-12-12 : 01:14:23

[code]SELECT Code,
Name,
SUM(LEN(InstallStatus) - LEN(REPLACE(InstallStatus, '=OK', ''))) / 3 AS [Total OK],
SUM(LEN(InstallStatus) - LEN(REPLACE(InstallStatus, '=NOTOK', ''))) / 6 AS [Total NOTOK]
FROM @test YourTableNameHere
GROUP BY Code,
Name
ORDER BY Code,
Name[/code]

Peter Larsson
Helsingborg, Sweden

surendra1
Starting Member

2 Posts

Posted - 2006-12-12 : 23:38:55

Thanks a lot Peso

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources