SQL 2000 View - 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

General SQL Server Forums

New to SQL Server Programming

SQL 2000 View

Author

Topic

afeets
Starting Member

10 Posts

Posted - 2007-12-24 : 10:36:29

The table I want to query contains data structure like so

tblText
-------
VisitId(int)CodeId(int) ChrText VarChar()
1 2 'Text Code2'
1 3 'Text Code3'
2 1 'Text Code1'
3 3 '*TextCode3*'
4 1 'Text Code1'
4 4 'Text COde4'

What I want is for my View to Look Like this

VisitId Code1 Code2 Code3 Code4
1 N/A Text Code2 Text Code3 N/A
2 Text Code1 N/A N/A N/A
3 n/a n/a *TextCode3* n/a
4 Text Code1 n/a n/a Text COde4

I am sure I Group by VisitId, but do not know the correct function to construct the rest of the Select Query

Create View vw_tblText
As
Select VisitId,
Case(intCodeId=1 Then chrText Else 'N/A' End) As Code1
Case(intCodeId=2 Then chrText Else 'N/A' End) As Code2
etc..
From tblText
Group by intVisit

Any Ideas much appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-24 : 11:09:56

Try this:-

Create View vw_tblText
As
select t1.VisitId,
       ISNULL(cd1.ChrText,'N/A') as 'Code1',
       ISNULL(cd2.ChrText,'N/A') as 'Code2',
       ISNULL(cd3.ChrText,'N/A') as 'Code3',
       ISNULL(cd4.ChrText,'N/A') as 'Code4'     
FROM tblText t1
LEFT OUTER JOIN (SELECT VisitId,ChrText
                 FROM tblText
                 WHERE CodeId=1) cd1
ON cd1.VisitId=t1.VisitId
LEFT OUTER JOIN (SELECT VisitId,ChrText
                 FROM tblText
                 WHERE CodeId=2) cd2
ON cd2.VisitId=t1.VisitId
LEFT OUTER JOIN (SELECT VisitId,ChrText
                 FROM tblText
                 WHERE CodeId=3) cd3
ON cd3.VisitId=t1.VisitId
LEFT OUTER JOIN (SELECT VisitId,ChrText
                 FROM tblText
                 WHERE CodeId=4) cd4
ON cd4.VisitId=t1.VisitId

afeets
Starting Member

10 Posts

Posted - 2007-12-27 : 05:37:51

Visakh16, thanks for getting back to me. The sample you gave me works fine as long as I group each each column in the select statement. There are actually a total of 20 Code Columns, should I be concerned putting so many columns in a Group by statement??

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 05:46:22

I didnt get you afeets. Which group by are you talking about? Is there a big main query of which this is a part of?

afeets
Starting Member

10 Posts

Posted - 2007-12-27 : 05:58:48

If you look at vw_tblText I tried to construct there should be a group by VisitId (not intVisit as I wrote!!). Now when I add the code you gave me, without the group by it show multiple records for the visitId, the number of records dependent on how many code fields have been filled in.

This view will be joined with another view showing the Officer's full report

Structure is below

View1 View2
----------------------------------------------------------*---------------------------------
visitId OfficerId SchoolId ActivityId DateOfVisit M1 M2 M3 M4 K1 K2 K3 K4 etc

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 05:59:58

quote:
Originally posted by visakh16

I didnt get you afeets. Which group by are you talking about? Is there a big main query of which this is a part of?

Ah i got you...i think you can also achieve this as follows:-

Create View vw_tblText
As

select t1.VisitId,
       ISNULL(cd1.ChrText,'N/A') as 'Code1',
       ISNULL(cd2.ChrText,'N/A') as 'Code2',
       ISNULL(cd3.ChrText,'N/A') as 'Code3',
       ISNULL(cd4.ChrText,'N/A') as 'Code4'     
FROM (SELECT DISTINCT VisitId FROM tblText) t1
LEFT OUTER JOIN (SELECT VisitId,ChrText
                 FROM tblText
                 WHERE CodeId=1) cd1
ON cd1.VisitId=t1.VisitId
LEFT OUTER JOIN (SELECT VisitId,ChrText
                 FROM tblText
                 WHERE CodeId=2) cd2
ON cd2.VisitId=t1.VisitId
LEFT OUTER JOIN (SELECT VisitId,ChrText
                 FROM tblText
                 WHERE CodeId=3) cd3
ON cd3.VisitId=t1.VisitId
LEFT OUTER JOIN (SELECT VisitId,ChrText
                 FROM tblText
                 WHERE CodeId=4) cd4
ON cd4.VisitId=t1.VisitId

afeets
Starting Member

10 Posts

Posted - 2007-12-27 : 06:08:53

That's great, thanks again for your help. Easy when you know how!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-27 : 06:13:20

Cheers.Please come back if you face any more problems.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources