|
|
You're in the
sql server programming
group:big problem!would you mind helping me pls?
sql server programming:
I have 3 tables: Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), PK:id TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk The question is here that I can create a query which results are: (id,Name,telno,telnotype,teltypes,address,comment) (4,nassa,091463738,2,Mobile,XXX,Null) (4,nassa,071163738,1,Tellphone,XXX,Nul) But,I want a query which shows the results in a way below: (id,Name,tellephone,mobile,Fax,e-mail,address,comment) (4,nassa,071163738,091463738,Null,Null,XXX,Null) Thanks, Nassa
Uri Dimant
Thank you so much for your help. there is another question that I make a view from them and make your query for them but it gives me an error. What should I do if I want to make a query fom views? because it helps in query performance. Thank you for your attention. Nassa [quoted text, click to view] Uri Dimant wrote:
> Nassa > > > create table #tmp (id int,name varchar(20), > telno varchar(20),telnotype int ,teltypes varchar(20),address > varchar(20),comment varchar(20)) > go > insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) > insert into #tmp values (4,'nassa','071163738',1,'Tellphone','XXX',Null) > go > select id,name, max(case when telnotype=1 then telno end) as 'Tellphone' > , max(case when telnotype=2 then telno end) as 'Mobile', > address,comment > from #tmp > group by id,name,address,comment > > > > > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > > Hi Everyone, > > > > I have 3 tables: > > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > > PK:id > > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > > > > The question is here that I can create a query which results are: > > (id,Name,telno,telnotype,teltypes,address,comment) > > (4,nassa,091463738,2,Mobile,XXX,Null) > > (4,nassa,071163738,1,Tellphone,XXX,Nul) > > > > But,I want a query which shows the results in a way below: > > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > > > > > > Thanks, > > Nassa > >
Uri Dimant,
Sorry,there is a mistake somewhere else.Thank you I found the answer. but it is important to using PIVOT table.Can you help me in that regard please? I am using SQL server 2005. thank you very much. Nassa [quoted text, click to view] Uri Dimant wrote:
> Nassa > Can you show us what did you do? What is the error? > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > > Uri Dimant > > Thank you so much for your help. > > there is another question that I make a view from them and make your > > query for them but it gives me an error. > > What should I do if I want to make a query fom views? because it helps > > in query performance. > > > > Thank you for your attention. > > Nassa > > Uri Dimant wrote: > >> Nassa > >> > >> > >> create table #tmp (id int,name varchar(20), > >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> varchar(20),comment varchar(20)) > >> go > >> insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> insert into #tmp values > >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> go > >> select id,name, max(case when telnotype=1 then telno end) as 'Tellphone' > >> , max(case when telnotype=2 then telno end) as 'Mobile', > >> address,comment > >> from #tmp > >> group by id,name,address,comment > >> > >> > >> > >> > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > >> > Hi Everyone, > >> > > >> > I have 3 tables: > >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > >> > PK:id > >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > >> > > >> > The question is here that I can create a query which results are: > >> > (id,Name,telno,telnotype,teltypes,address,comment) > >> > (4,nassa,091463738,2,Mobile,XXX,Null) > >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) > >> > > >> > But,I want a query which shows the results in a way below: > >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > >> > > >> > > >> > Thanks, > >> > Nassa > >> > > >
Uri Dimant
Thank you so much and so sorry, the mistake is from me,myself! your query is completely right and correct.Another qustion: can you please tell me about pivot table because I want those info. by using PIVOT. Thank you so much. Nassa [quoted text, click to view] Uri Dimant wrote:
> Nassa > Can you show us what did you do? What is the error? > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > > Uri Dimant > > Thank you so much for your help. > > there is another question that I make a view from them and make your > > query for them but it gives me an error. > > What should I do if I want to make a query fom views? because it helps > > in query performance. > > > > Thank you for your attention. > > Nassa > > Uri Dimant wrote: > >> Nassa > >> > >> > >> create table #tmp (id int,name varchar(20), > >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> varchar(20),comment varchar(20)) > >> go > >> insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> insert into #tmp values > >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> go > >> select id,name, max(case when telnotype=1 then telno end) as 'Tellphone' > >> , max(case when telnotype=2 then telno end) as 'Mobile', > >> address,comment > >> from #tmp > >> group by id,name,address,comment > >> > >> > >> > >> > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > >> > Hi Everyone, > >> > > >> > I have 3 tables: > >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > >> > PK:id > >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > >> > > >> > The question is here that I can create a query which results are: > >> > (id,Name,telno,telnotype,teltypes,address,comment) > >> > (4,nassa,091463738,2,Mobile,XXX,Null) > >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) > >> > > >> > But,I want a query which shows the results in a way below: > >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > >> > > >> > > >> > Thanks, > >> > Nassa > >> > > >
Uri Dimant,
---------------------------------------------------------------------------------------------------------------------------------------------------------------------- the error is : The text,ntext, and image data types can not be compared or stored, excep when using IS NULL or LIKE operator. ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- after I am writting this: SELECT TOP (100) PERCENT dbo.TelBook.ID, dbo.TelBook.Name, MAX(CASE WHEN telnotype = 1 THEN telno END) AS Tellphone, MAX(CASE WHEN telnotype = 2 THEN telno END) AS Mobile, MAX(CASE WHEN telnotype = 3 THEN telno END) AS Fax, MAX(CASE WHEN telnotype = 4 THEN telno END) AS Email,telbook.comment,telbook.address,telbook.comment FROM dbo.TelTypes INNER JOIN dbo.TelNumbers ON dbo.TelTypes.FK = dbo.TelNumbers.TelNoType RIGHT OUTER JOIN dbo.TelBook ON dbo.TelNumbers.TelBookID = dbo.TelBook.ID GROUP BY dbo.TelBook.ID, dbo.TelBook.Name,telbook.comment,telbook.address,telbook.comment ORDER BY dbo.TelBook.ID Thanks, Nassa [quoted text, click to view] Uri Dimant wrote:
> Nassa > Can you show us what did you do? What is the error? > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > > Uri Dimant > > Thank you so much for your help. > > there is another question that I make a view from them and make your > > query for them but it gives me an error. > > What should I do if I want to make a query fom views? because it helps > > in query performance. > > > > Thank you for your attention. > > Nassa > > Uri Dimant wrote: > >> Nassa > >> > >> > >> create table #tmp (id int,name varchar(20), > >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> varchar(20),comment varchar(20)) > >> go > >> insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> insert into #tmp values > >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> go > >> select id,name, max(case when telnotype=1 then telno end) as 'Tellphone' > >> , max(case when telnotype=2 then telno end) as 'Mobile', > >> address,comment > >> from #tmp > >> group by id,name,address,comment > >> > >> > >> > >> > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > >> > Hi Everyone, > >> > > >> > I have 3 tables: > >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > >> > PK:id > >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > >> > > >> > The question is here that I can create a query which results are: > >> > (id,Name,telno,telnotype,teltypes,address,comment) > >> > (4,nassa,091463738,2,Mobile,XXX,Null) > >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) > >> > > >> > But,I want a query which shows the results in a way below: > >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > >> > > >> > > >> > Thanks, > >> > Nassa > >> > > >
Erland,
I just add order by,but when I execute the query in SQL server 2005, it is automatically add TOP 100 Percent to the first of query.Its not my fault. by the way,the problem of that is that I cant not use ntext when I want to compare,I dnt know where the real problem is. anyway,can you please help me writing a query by using pivot when I want the specific result which I have mentioned befor? thanks Nassa [quoted text, click to view] Erland Sommarskog wrote:
> Nassa (nassim.czdashti@gmail.com) writes: > > the error is : > > The text,ntext, and image data types can not be compared or stored, > > excep when using IS NULL or LIKE operator. > > But you were using SQL 2005, weren't you? In that case, you should > seriously consider to use the nvarchar(MAX) data type rather than ntext. > nvarchar(MAX) fits as much data as ntext, but does not have all the > restrictions that comes with it. > > If you are not responsible of the data model yourself, you should convince > the person who is to make a change. Life without ntext is so much easier. > > > SELECT TOP (100) PERCENT dbo.TelBook.ID, dbo.TelBook.Name, MAX(CASE > >... > > ORDER BY dbo.TelBook.ID > > Note that this TOP 100 PERCENT is completely meaningless. TOP 100 PERCENT > means everything, so there is no need to specify it. In SQL 2000, if you > put TOP 100 PERCENT + ORDER BY in a view, and then selected from the view > without the ORDER BY, the results often came back in the same order as the > ORDER BY clause in the view. This happens far less often in SQL 2005. The > only way to get an ordered result from a query is to add ORDER BY to it. > > > -- > Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se > > Books Online for SQL Server 2005 at > http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx > Books Online for SQL Server 2000 at > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Uri Dimant,
I gave you all the information that anyone can need. Which type of information do you need? As I told you before I have had those tables-telbook,telnumbers,teltypes-Now,I want to find a query that I can mach with that specific result that I have shown you in the first request massage. Thanks, Nassa [quoted text, click to view] Uri Dimant wrote:
> Nassa > Now you are seeng , instead of providing a full information about your > tables (dataypes,keys) to prevent from misundertanding and additional > questions > you are giving us piece by piece of info about your data to make use > guessing to solve the problem > > > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167294048.678344.34280@i12g2000cwa.googlegroups.com... > > Uri Dimant, > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > the error is : > > The text,ntext, and image data types can not be compared or stored, > > excep when using IS NULL or LIKE operator. > > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > > > after I am writting this: > > SELECT TOP (100) PERCENT dbo.TelBook.ID, dbo.TelBook.Name, MAX(CASE > > WHEN telnotype = 1 THEN telno END) AS Tellphone, > > MAX(CASE WHEN telnotype = 2 THEN telno END) AS > > Mobile, MAX(CASE WHEN telnotype = 3 THEN telno END) AS Fax, > > MAX(CASE WHEN telnotype = 4 THEN telno END) AS > > Email,telbook.comment,telbook.address,telbook.comment > > FROM dbo.TelTypes INNER JOIN > > dbo.TelNumbers ON dbo.TelTypes.FK = > > dbo.TelNumbers.TelNoType RIGHT OUTER JOIN > > dbo.TelBook ON dbo.TelNumbers.TelBookID = > > dbo.TelBook.ID > > GROUP BY dbo.TelBook.ID, > > dbo.TelBook.Name,telbook.comment,telbook.address,telbook.comment > > ORDER BY dbo.TelBook.ID > > > > Thanks, > > Nassa > > > > > > Uri Dimant wrote: > >> Nassa > >> Can you show us what did you do? What is the error? > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > >> > Uri Dimant > >> > Thank you so much for your help. > >> > there is another question that I make a view from them and make your > >> > query for them but it gives me an error. > >> > What should I do if I want to make a query fom views? because it helps > >> > in query performance. > >> > > >> > Thank you for your attention. > >> > Nassa > >> > Uri Dimant wrote: > >> >> Nassa > >> >> > >> >> > >> >> create table #tmp (id int,name varchar(20), > >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> varchar(20),comment varchar(20)) > >> >> go > >> >> insert into #tmp values > >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> insert into #tmp values > >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> >> go > >> >> select id,name, max(case when telnotype=1 then telno end) as > >> >> 'Tellphone' > >> >> , max(case when telnotype=2 then telno end) as 'Mobile', > >> >> address,comment > >> >> from #tmp > >> >> group by id,name,address,comment > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > >> >> > Hi Everyone, > >> >> > > >> >> > I have 3 tables: > >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > >> >> > PK:id > >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > >> >> > > >> >> > The question is here that I can create a query which results are: > >> >> > (id,Name,telno,telnotype,teltypes,address,comment) > >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) > >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) > >> >> > > >> >> > But,I want a query which shows the results in a way below: > >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > >> >> > > >> >> > > >> >> > Thanks, > >> >> > Nassa > >> >> > > >> > > >
Uri Dimant,
Thank you so much, I checked that article. Thank you so much for all other information. I will be contact you as much as I can.hope to not to be tired of me! Thanks, Nassa [quoted text, click to view] Uri Dimant wrote:
> Nassa > Have you read this article? > http://www.aspfaq.com/etiquette.asp?id=5006 > > > > > > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167296588.452076.114110@42g2000cwt.googlegroups.com... > > Uri Dimant, > > I gave you all the information that anyone can need. > > Which type of information do you need? > > As I told you before I have had those > > tables-telbook,telnumbers,teltypes-Now,I want to find a query that I > > can mach with that specific result that I have shown you in the first > > request massage. > > > > Thanks, > > Nassa > > > > Uri Dimant wrote: > >> Nassa > >> Now you are seeng , instead of providing a full information about your > >> tables (dataypes,keys) to prevent from misundertanding and additional > >> questions > >> you are giving us piece by piece of info about your data to make use > >> guessing to solve the problem > >> > >> > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167294048.678344.34280@i12g2000cwa.googlegroups.com... > >> > Uri Dimant, > >> > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- > >> > the error is : > >> > The text,ntext, and image data types can not be compared or stored, > >> > excep when using IS NULL or LIKE operator. > >> > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- > >> > > >> > after I am writting this: > >> > SELECT TOP (100) PERCENT dbo.TelBook.ID, dbo.TelBook.Name, MAX(CASE > >> > WHEN telnotype = 1 THEN telno END) AS Tellphone, > >> > MAX(CASE WHEN telnotype = 2 THEN telno END) AS > >> > Mobile, MAX(CASE WHEN telnotype = 3 THEN telno END) AS Fax, > >> > MAX(CASE WHEN telnotype = 4 THEN telno END) AS > >> > Email,telbook.comment,telbook.address,telbook.comment > >> > FROM dbo.TelTypes INNER JOIN > >> > dbo.TelNumbers ON dbo.TelTypes.FK = > >> > dbo.TelNumbers.TelNoType RIGHT OUTER JOIN > >> > dbo.TelBook ON dbo.TelNumbers.TelBookID = > >> > dbo.TelBook.ID > >> > GROUP BY dbo.TelBook.ID, > >> > dbo.TelBook.Name,telbook.comment,telbook.address,telbook.comment > >> > ORDER BY dbo.TelBook.ID > >> > > >> > Thanks, > >> > Nassa > >> > > >> > > >> > Uri Dimant wrote: > >> >> Nassa > >> >> Can you show us what did you do? What is the error? > >> >> > >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > >> >> > Uri Dimant > >> >> > Thank you so much for your help. > >> >> > there is another question that I make a view from them and make your > >> >> > query for them but it gives me an error. > >> >> > What should I do if I want to make a query fom views? because it > >> >> > helps > >> >> > in query performance. > >> >> > > >> >> > Thank you for your attention. > >> >> > Nassa > >> >> > Uri Dimant wrote: > >> >> >> Nassa > >> >> >> > >> >> >> > >> >> >> create table #tmp (id int,name varchar(20), > >> >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> >> varchar(20),comment varchar(20)) > >> >> >> go > >> >> >> insert into #tmp values > >> >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> >> insert into #tmp values > >> >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> >> >> go > >> >> >> select id,name, max(case when telnotype=1 then telno end) as > >> >> >> 'Tellphone' > >> >> >> , max(case when telnotype=2 then telno end) as > >> >> >> 'Mobile', > >> >> >> address,comment > >> >> >> from #tmp > >> >> >> group by id,name,address,comment > >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > >> >> >> > Hi Everyone, > >> >> >> > > >> >> >> > I have 3 tables: > >> >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > >> >> >> > PK:id > >> >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > >> >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > >> >> >> > > >> >> >> > The question is here that I can create a query which results are: > >> >> >> > (id,Name,telno,telnotype,teltypes,address,comment) > >> >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) > >> >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) > >> >> >> > > >> >> >> > But,I want a query which shows the results in a way below: > >> >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > >> >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > >> >> >> > > >> >> >> > > >> >> >> > Thanks, > >> >> >> > Nassa > >> >> >> > > >> >> > > >> > > >
Uri Dimant,
Thank you for PIVOT query, I checked it out but it gives me an error: ---------------------------------------------------------------------------------------------------------------------------- The multi-part identifier "telbook.id" could not be bound. The multi-part identifier "telbook.name" could not be bound. The multi-part identifier "telbook.address" could not be bound. The multi-part identifier "telbook.comment" could not be bound. The multi-part identifier "telbook.id" could not be bound. The multi-part identifier "telbook.name" could not be bound. The multi-part identifier "telbook.address" could not be bound. The multi-part identifier "telbook.comment" could not be bound. ------------------------------------------------------------------------------------------------------------------------------- The query is like below: ------------------------------------------------------------------------------------------------------------------------ SELECT telbook.id,telbook.name,telbook.address,telbook.comment,max([telphone])[telphone],max([mobile])[mobile] FROM dbo.TelBook INNER JOIN dbo.TelNumbers ON dbo.TelBook.ID = dbo.TelNumbers.TelBookID JOIN dbo.TelTypes ON teltypes.fk=telnumbers.telnotype PIVOT (max(telno) FOR teltypes IN ([telphone],[mobile]) ) as PVT Group By telbook.id,telbook.name,telbook.address,telbook.comment [quoted text, click to view] Uri Dimant wrote:
> Nassa > create table #tmp (id int,name varchar(20), > > telno varchar(20),telnotype int ,teltypes varchar(20),address > > varchar(20),comment varchar(20)) > > go > > insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) > > insert into #tmp values (4,'nassa','071163738',1,'Tellphone','XXX',Null) > > go > > select > id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > > from #tmp > > pivot > > ( > > max(telno) > > for teltypes IN([Mobile],[Tellphone]) > > ) as PVT > > group by id,name,address,comment > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167292833.810694.305560@i12g2000cwa.googlegroups.com... > > Uri Dimant, > > > > Sorry,there is a mistake somewhere else.Thank you I found the answer. > > but it is important to using PIVOT table.Can you help me in that regard > > please? > > I am using SQL server 2005. > > > > thank you very much. > > Nassa > > > > Uri Dimant wrote: > >> Nassa > >> Can you show us what did you do? What is the error? > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > >> > Uri Dimant > >> > Thank you so much for your help. > >> > there is another question that I make a view from them and make your > >> > query for them but it gives me an error. > >> > What should I do if I want to make a query fom views? because it helps > >> > in query performance. > >> > > >> > Thank you for your attention. > >> > Nassa > >> > Uri Dimant wrote: > >> >> Nassa > >> >> > >> >> > >> >> create table #tmp (id int,name varchar(20), > >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> varchar(20),comment varchar(20)) > >> >> go > >> >> insert into #tmp values > >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> insert into #tmp values > >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> >> go > >> >> select id,name, max(case when telnotype=1 then telno end) as > >> >> 'Tellphone' > >> >> , max(case when telnotype=2 then telno end) as 'Mobile', > >> >> address,comment > >> >> from #tmp > >> >> group by id,name,address,comment > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > >> >> > Hi Everyone, > >> >> > > >> >> > I have 3 tables: > >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > >> >> > PK:id > >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > >> >> > > >> >> > The question is here that I can create a query which results are: > >> >> > (id,Name,telno,telnotype,teltypes,address,comment) > >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) > >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) > >> >> > > >> >> > But,I want a query which shows the results in a way below: > >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > >> >> > > >> >> > > >> >> > Thanks, > >> >> > Nassa > >> >> > > >> > > >
Uri Dimant,
What does "With PNT" mean? Thanks, Nassa [quoted text, click to view] Uri Dimant wrote:
> Nassa > Untested > > WITH PNT > > AS > > ( > > SELECT TB.* > > FROM dbo.TelBook TB INNER JOIN > > dbo.TelNumbers TN ON TB.ID =TN.TelBookID > > INNER JOIN dbo.TelTypes TT ON TT.fk=TN.telnotype > > ) > > SELECT > id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > > FROM PNT > > PIVOT > > ( > > MAX(telno) > > FOR teltypes IN([Mobile],[Tellphone]) > > ) AS PVT > > GROUP BY id,name,address,comment > > > > > > > > > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167299136.583040.19030@73g2000cwn.googlegroups.com... > > Uri Dimant, > > Thank you for PIVOT query, > > I checked it out but it gives me an error: > > ---------------------------------------------------------------------------------------------------------------------------- > > The multi-part identifier "telbook.id" could not be bound. > > The multi-part identifier "telbook.name" could not be bound. > > The multi-part identifier "telbook.address" could not be bound. > > The multi-part identifier "telbook.comment" could not be bound. > > The multi-part identifier "telbook.id" could not be bound. > > The multi-part identifier "telbook.name" could not be bound. > > The multi-part identifier "telbook.address" could not be bound. > > The multi-part identifier "telbook.comment" could not be bound. > > ------------------------------------------------------------------------------------------------------------------------------- > > The query is like below: > > ------------------------------------------------------------------------------------------------------------------------ > > SELECT > > telbook.id,telbook.name,telbook.address,telbook.comment,max([telphone])[telphone],max([mobile])[mobile] > > FROM dbo.TelBook INNER JOIN > > dbo.TelNumbers ON dbo.TelBook.ID = > > dbo.TelNumbers.TelBookID JOIN > > dbo.TelTypes ON teltypes.fk=telnumbers.telnotype > > PIVOT > > (max(telno) > > FOR teltypes IN ([telphone],[mobile]) > > ) as PVT > > Group By telbook.id,telbook.name,telbook.address,telbook.comment > > > > Uri Dimant wrote: > >> Nassa > >> create table #tmp (id int,name varchar(20), > >> > >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> > >> varchar(20),comment varchar(20)) > >> > >> go > >> > >> insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> > >> insert into #tmp values (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> > >> go > >> > >> select > >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > >> > >> from #tmp > >> > >> pivot > >> > >> ( > >> > >> max(telno) > >> > >> for teltypes IN([Mobile],[Tellphone]) > >> > >> ) as PVT > >> > >> group by id,name,address,comment > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167292833.810694.305560@i12g2000cwa.googlegroups.com... > >> > Uri Dimant, > >> > > >> > Sorry,there is a mistake somewhere else.Thank you I found the answer. > >> > but it is important to using PIVOT table.Can you help me in that regard > >> > please? > >> > I am using SQL server 2005. > >> > > >> > thank you very much. > >> > Nassa > >> > > >> > Uri Dimant wrote: > >> >> Nassa > >> >> Can you show us what did you do? What is the error? > >> >> > >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > >> >> > Uri Dimant > >> >> > Thank you so much for your help. > >> >> > there is another question that I make a view from them and make your > >> >> > query for them but it gives me an error. > >> >> > What should I do if I want to make a query fom views? because it > >> >> > helps > >> >> > in query performance. > >> >> > > >> >> > Thank you for your attention. > >> >> > Nassa > >> >> > Uri Dimant wrote: > >> >> >> Nassa > >> >> >> > >> >> >> > >> >> >> create table #tmp (id int,name varchar(20), > >> >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> >> varchar(20),comment varchar(20)) > >> >> >> go > >> >> >> insert into #tmp values > >> >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> >> insert into #tmp values > >> >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> >> >> go > >> >> >> select id,name, max(case when telnotype=1 then telno end) as > >> >> >> 'Tellphone' > >> >> >> , max(case when telnotype=2 then telno end) as > >> >> >> 'Mobile', > >> >> >> address,comment > >> >> >> from #tmp > >> >> >> group by id,name,address,comment > >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> > >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > >> >> >> > Hi Everyone, > >> >> >> > > >> >> >> > I have 3 tables: > >> >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > >> >> >> > PK:id > >> >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > >> >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > >> >> >> > > >> >> >> > The question is here that I can create a query which results are: > >> >> >> > (id,Name,telno,telnotype,teltypes,address,comment) > >> >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) > >> >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) > >> >> >> > > >> >> >> > But,I want a query which shows the results in a way below: > >> >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > >> >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > >> >> >> > > >> >> >> > > >> >> >> > Thanks, > >> >> >> > Nassa > >> >> >> > > >> >> > > >> > > >
Uri Dimant
Thank you. I tested the query(PVT one!), but it gives me an error again.here is the error: ------------------------------------------------------------- invalid column name 'telno'. invalid column name 'teltypes'. ------------------------------------------------------------- Thanks, Nassa [quoted text, click to view] Uri Dimant wrote:
> CTE-Common Table Expression. It was introduced in SQL Server 2005. > > > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167300532.067806.254930@79g2000cws.googlegroups.com... > > Uri Dimant, > > > > What does "With PNT" mean? > > > > Thanks, > > Nassa > > > > Uri Dimant wrote: > >> Nassa > >> Untested > >> > >> WITH PNT > >> > >> AS > >> > >> ( > >> > >> SELECT TB.* > >> > >> FROM dbo.TelBook TB INNER JOIN > >> > >> dbo.TelNumbers TN ON TB.ID =TN.TelBookID > >> > >> INNER JOIN dbo.TelTypes TT ON TT.fk=TN.telnotype > >> > >> ) > >> > >> SELECT > >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > >> > >> FROM PNT > >> > >> PIVOT > >> > >> ( > >> > >> MAX(telno) > >> > >> FOR teltypes IN([Mobile],[Tellphone]) > >> > >> ) AS PVT > >> > >> GROUP BY id,name,address,comment > >> > >> > >> > >> > >> > >> > >> > >> > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167299136.583040.19030@73g2000cwn.googlegroups.com... > >> > Uri Dimant, > >> > Thank you for PIVOT query, > >> > I checked it out but it gives me an error: > >> > ---------------------------------------------------------------------------------------------------------------------------- > >> > The multi-part identifier "telbook.id" could not be bound. > >> > The multi-part identifier "telbook.name" could not be bound. > >> > The multi-part identifier "telbook.address" could not be bound. > >> > The multi-part identifier "telbook.comment" could not be bound. > >> > The multi-part identifier "telbook.id" could not be bound. > >> > The multi-part identifier "telbook.name" could not be bound. > >> > The multi-part identifier "telbook.address" could not be bound. > >> > The multi-part identifier "telbook.comment" could not be bound. > >> > ------------------------------------------------------------------------------------------------------------------------------- > >> > The query is like below: > >> > ------------------------------------------------------------------------------------------------------------------------ > >> > SELECT > >> > telbook.id,telbook.name,telbook.address,telbook.comment,max([telphone])[telphone],max([mobile])[mobile] > >> > FROM dbo.TelBook INNER JOIN > >> > dbo.TelNumbers ON dbo.TelBook.ID = > >> > dbo.TelNumbers.TelBookID JOIN > >> > dbo.TelTypes ON teltypes.fk=telnumbers.telnotype > >> > PIVOT > >> > (max(telno) > >> > FOR teltypes IN ([telphone],[mobile]) > >> > ) as PVT > >> > Group By telbook.id,telbook.name,telbook.address,telbook.comment > >> > > >> > Uri Dimant wrote: > >> >> Nassa > >> >> create table #tmp (id int,name varchar(20), > >> >> > >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> > >> >> varchar(20),comment varchar(20)) > >> >> > >> >> go > >> >> > >> >> insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> > >> >> insert into #tmp values > >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> >> > >> >> go > >> >> > >> >> select > >> >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > >> >> > >> >> from #tmp > >> >> > >> >> pivot > >> >> > >> >> ( > >> >> > >> >> max(telno) > >> >> > >> >> for teltypes IN([Mobile],[Tellphone]) > >> >> > >> >> ) as PVT > >> >> > >> >> group by id,name,address,comment > >> >> > >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> news:1167292833.810694.305560@i12g2000cwa.googlegroups.com... > >> >> > Uri Dimant, > >> >> > > >> >> > Sorry,there is a mistake somewhere else.Thank you I found the > >> >> > answer. > >> >> > but it is important to using PIVOT table.Can you help me in that > >> >> > regard > >> >> > please? > >> >> > I am using SQL server 2005. > >> >> > > >> >> > thank you very much. > >> >> > Nassa > >> >> > > >> >> > Uri Dimant wrote: > >> >> >> Nassa > >> >> >> Can you show us what did you do? What is the error? > >> >> >> > >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > >> >> >> > Uri Dimant > >> >> >> > Thank you so much for your help. > >> >> >> > there is another question that I make a view from them and make > >> >> >> > your > >> >> >> > query for them but it gives me an error. > >> >> >> > What should I do if I want to make a query fom views? because it > >> >> >> > helps > >> >> >> > in query performance. > >> >> >> > > >> >> >> > Thank you for your attention. > >> >> >> > Nassa > >> >> >> > Uri Dimant wrote: > >> >> >> >> Nassa > >> >> >> >> > >> >> >> >> > >> >> >> >> create table #tmp (id int,name varchar(20), > >> >> >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> >> >> varchar(20),comment varchar(20)) > >> >> >> >> go > >> >> >> >> insert into #tmp values > >> >> >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> >> >> insert into #tmp values > >> >> >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> >> >> >> go > >> >> >> >> select id,name, max(case when telnotype=1 then telno end) as > >> >> >> >> 'Tellphone' > >> >> >> >> , max(case when telnotype=2 then telno end) as > >> >> >> >> 'Mobile', > >> >> >> >> address,comment > >> >> >> >> from #tmp > >> >> >> >> group by id,name,address,comment > >> >> >> >> > >> >> >> >> > >> >> >> >> > >> >> >> >> > >> >> >> >> > >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > >> >> >> >> > Hi Everyone, > >> >> >> >> > > >> >> >> >> > I have 3 tables: > >> >> >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > >> >> >> >> > PK:id > >> >> >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > >> >> >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > >> >> >> >> > > >> >> >> >> > The question is here that I can create a query which results > >> >> >> >> > are: > >> >> >> >> > (id,Name,telno,telnotype,teltypes,address,comment) > >> >> >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) > >> >> >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) > >> >> >> >> > > >> >> >> >> > But,I want a query which shows the results in a way below: > >> >> >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > >> >> >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > >> >> >> >> >
Uri Dimant,
Thank you very very .... much.Its completely correct. I really do appriciate it. u ask a question in Query cost.in that regard,I just want to compare these two queries,because there is another way to find the result-by creating function- in order to find which one is better,I need to find query cost or query performance. Thanks, Nassa [quoted text, click to view] Uri Dimant wrote:
> Try > WITH PNT > > AS > > ( > > SELECT TB.*,TN*,TT.* > > FROM dbo.TelBook TB INNER JOIN > > dbo.TelNumbers TN ON TB.ID =TN.TelBookID > > INNER JOIN dbo.TelTypes TT ON TT.fk=TN.telnotype > > ) > > SELECT > id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > > FROM PNT > > PIVOT > > ( > > MAX(telno) > > FOR teltypes IN([Mobile],[Tellphone]) > > ) AS PVT > > GROUP BY id,name,address,comment > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167306746.160217.7920@i12g2000cwa.googlegroups.com... > > Uri Dimant > > > > Thank you. > > I tested the query(PVT one!), but it gives me an error again.here is > > the error: > > ------------------------------------------------------------- > > invalid column name 'telno'. > > invalid column name 'teltypes'. > > ------------------------------------------------------------- > > > > Thanks, > > Nassa > > > > Uri Dimant wrote: > >> CTE-Common Table Expression. It was introduced in SQL Server 2005. > >> > >> > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167300532.067806.254930@79g2000cws.googlegroups.com... > >> > Uri Dimant, > >> > > >> > What does "With PNT" mean? > >> > > >> > Thanks, > >> > Nassa > >> > > >> > Uri Dimant wrote: > >> >> Nassa > >> >> Untested > >> >> > >> >> WITH PNT > >> >> > >> >> AS > >> >> > >> >> ( > >> >> > >> >> SELECT TB.* > >> >> > >> >> FROM dbo.TelBook TB INNER JOIN > >> >> > >> >> dbo.TelNumbers TN ON TB.ID =TN.TelBookID > >> >> > >> >> INNER JOIN dbo.TelTypes TT ON TT.fk=TN.telnotype > >> >> > >> >> ) > >> >> > >> >> SELECT > >> >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > >> >> > >> >> FROM PNT > >> >> > >> >> PIVOT > >> >> > >> >> ( > >> >> > >> >> MAX(telno) > >> >> > >> >> FOR teltypes IN([Mobile],[Tellphone]) > >> >> > >> >> ) AS PVT > >> >> > >> >> GROUP BY id,name,address,comment > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> news:1167299136.583040.19030@73g2000cwn.googlegroups.com... > >> >> > Uri Dimant, > >> >> > Thank you for PIVOT query, > >> >> > I checked it out but it gives me an error: > >> >> > ---------------------------------------------------------------------------------------------------------------------------- > >> >> > The multi-part identifier "telbook.id" could not be bound. > >> >> > The multi-part identifier "telbook.name" could not be bound. > >> >> > The multi-part identifier "telbook.address" could not be bound. > >> >> > The multi-part identifier "telbook.comment" could not be bound. > >> >> > The multi-part identifier "telbook.id" could not be bound. > >> >> > The multi-part identifier "telbook.name" could not be bound. > >> >> > The multi-part identifier "telbook.address" could not be bound. > >> >> > The multi-part identifier "telbook.comment" could not be bound. > >> >> > ------------------------------------------------------------------------------------------------------------------------------- > >> >> > The query is like below: > >> >> > ------------------------------------------------------------------------------------------------------------------------ > >> >> > SELECT > >> >> > telbook.id,telbook.name,telbook.address,telbook.comment,max([telphone])[telphone],max([mobile])[mobile] > >> >> > FROM dbo.TelBook INNER JOIN > >> >> > dbo.TelNumbers ON dbo.TelBook.ID = > >> >> > dbo.TelNumbers.TelBookID JOIN > >> >> > dbo.TelTypes ON > >> >> > teltypes.fk=telnumbers.telnotype > >> >> > PIVOT > >> >> > (max(telno) > >> >> > FOR teltypes IN ([telphone],[mobile]) > >> >> > ) as PVT > >> >> > Group By telbook.id,telbook.name,telbook.address,telbook.comment > >> >> > > >> >> > Uri Dimant wrote: > >> >> >> Nassa > >> >> >> create table #tmp (id int,name varchar(20), > >> >> >> > >> >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> >> > >> >> >> varchar(20),comment varchar(20)) > >> >> >> > >> >> >> go > >> >> >> > >> >> >> insert into #tmp values > >> >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> >> > >> >> >> insert into #tmp values > >> >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> >> >> > >> >> >> go > >> >> >> > >> >> >> select > >> >> >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > >> >> >> > >> >> >> from #tmp > >> >> >> > >> >> >> pivot > >> >> >> > >> >> >> ( > >> >> >> > >> >> >> max(telno) > >> >> >> > >> >> >> for teltypes IN([Mobile],[Tellphone]) > >> >> >> > >> >> >> ) as PVT > >> >> >> > >> >> >> group by id,name,address,comment > >> >> >> > >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> >> news:1167292833.810694.305560@i12g2000cwa.googlegroups.com... > >> >> >> > Uri Dimant, > >> >> >> > > >> >> >> > Sorry,there is a mistake somewhere else.Thank you I found the > >> >> >> > answer. > >> >> >> > but it is important to using PIVOT table.Can you help me in that > >> >> >> > regard > >> >> >> > please? > >> >> >> > I am using SQL server 2005. > >> >> >> > > >> >> >> > thank you very much. > >> >> >> > Nassa > >> >> >> > > >> >> >> > Uri Dimant wrote: > >> >> >> >> Nassa > >> >> >> >> Can you show us what did you do? What is the error? > >> >> >> >> > >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> >> >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > >> >> >> >> > Uri Dimant > >> >> >> >> > Thank you so much for your help. > >> >> >> >> > there is another question that I make a view from them and > >> >> >> >> > make > >> >> >> >> > your > >> >> >> >> > query for them but it gives me an error. > >> >> >> >> > What should I do if I want to make a query fom views? because > >> >> >> >> > it > >> >> >> >> > helps > >> >> >> >> > in query performance. > >> >> >> >> > > >> >> >> >> > Thank you for your attention. > >> >> >> >> > Nassa > >> >> >> >> > Uri Dimant wrote: > >> >> >> >> >> Nassa > >> >> >> >> >> > >> >> >> >> >> > >> >> >> >> >> create table #tmp (id int,name varchar(20), > >> >> >> >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> >> >> >> varchar(20),comment varchar(20)) > >> >> >> >> >> go > >> >> >> >> >> insert into #tmp values > >> >> >> >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> >> >> >> insert into #tmp values
Uri Dimant,
Sory,I check the query again but it gives me an error,again:(. The error is completely different!it is a query definitions differ which writes: ---------------------------------------------------------------------------------------------------------------------------------------------- The Following errors were encountered while parsing the contents of the SQL pane: The PIVOT SQL construct or statement is not supported. ----------------------------------------------------------------------------------------------------------------------------------------------- What should I do now? also it doesnt contain the values of mobile and tel numbers. thanks, Nassa [quoted text, click to view] Uri Dimant wrote:
> Try > WITH PNT > > AS > > ( > > SELECT TB.*,TN*,TT.* > > FROM dbo.TelBook TB INNER JOIN > > dbo.TelNumbers TN ON TB.ID =TN.TelBookID > > INNER JOIN dbo.TelTypes TT ON TT.fk=TN.telnotype > > ) > > SELECT > id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > > FROM PNT > > PIVOT > > ( > > MAX(telno) > > FOR teltypes IN([Mobile],[Tellphone]) > > ) AS PVT > > GROUP BY id,name,address,comment > > "Nassa" <nassim.czdashti@gmail.com> wrote in message > news:1167306746.160217.7920@i12g2000cwa.googlegroups.com... > > Uri Dimant > > > > Thank you. > > I tested the query(PVT one!), but it gives me an error again.here is > > the error: > > ------------------------------------------------------------- > > invalid column name 'telno'. > > invalid column name 'teltypes'. > > ------------------------------------------------------------- > > > > Thanks, > > Nassa > > > > Uri Dimant wrote: > >> CTE-Common Table Expression. It was introduced in SQL Server 2005. > >> > >> > >> > >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> news:1167300532.067806.254930@79g2000cws.googlegroups.com... > >> > Uri Dimant, > >> > > >> > What does "With PNT" mean? > >> > > >> > Thanks, > >> > Nassa > >> > > >> > Uri Dimant wrote: > >> >> Nassa > >> >> Untested > >> >> > >> >> WITH PNT > >> >> > >> >> AS > >> >> > >> >> ( > >> >> > >> >> SELECT TB.* > >> >> > >> >> FROM dbo.TelBook TB INNER JOIN > >> >> > >> >> dbo.TelNumbers TN ON TB.ID =TN.TelBookID > >> >> > >> >> INNER JOIN dbo.TelTypes TT ON TT.fk=TN.telnotype > >> >> > >> >> ) > >> >> > >> >> SELECT > >> >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > >> >> > >> >> FROM PNT > >> >> > >> >> PIVOT > >> >> > >> >> ( > >> >> > >> >> MAX(telno) > >> >> > >> >> FOR teltypes IN([Mobile],[Tellphone]) > >> >> > >> >> ) AS PVT > >> >> > >> >> GROUP BY id,name,address,comment > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> > >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> news:1167299136.583040.19030@73g2000cwn.googlegroups.com... > >> >> > Uri Dimant, > >> >> > Thank you for PIVOT query, > >> >> > I checked it out but it gives me an error: > >> >> > ---------------------------------------------------------------------------------------------------------------------------- > >> >> > The multi-part identifier "telbook.id" could not be bound. > >> >> > The multi-part identifier "telbook.name" could not be bound. > >> >> > The multi-part identifier "telbook.address" could not be bound. > >> >> > The multi-part identifier "telbook.comment" could not be bound. > >> >> > The multi-part identifier "telbook.id" could not be bound. > >> >> > The multi-part identifier "telbook.name" could not be bound. > >> >> > The multi-part identifier "telbook.address" could not be bound. > >> >> > The multi-part identifier "telbook.comment" could not be bound. > >> >> > ------------------------------------------------------------------------------------------------------------------------------- > >> >> > The query is like below: > >> >> > ------------------------------------------------------------------------------------------------------------------------ > >> >> > SELECT > >> >> > telbook.id,telbook.name,telbook.address,telbook.comment,max([telphone])[telphone],max([mobile])[mobile] > >> >> > FROM dbo.TelBook INNER JOIN > >> >> > dbo.TelNumbers ON dbo.TelBook.ID = > >> >> > dbo.TelNumbers.TelBookID JOIN > >> >> > dbo.TelTypes ON > >> >> > teltypes.fk=telnumbers.telnotype > >> >> > PIVOT > >> >> > (max(telno) > >> >> > FOR teltypes IN ([telphone],[mobile]) > >> >> > ) as PVT > >> >> > Group By telbook.id,telbook.name,telbook.address,telbook.comment > >> >> > > >> >> > Uri Dimant wrote: > >> >> >> Nassa > >> >> >> create table #tmp (id int,name varchar(20), > >> >> >> > >> >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address > >> >> >> > >> >> >> varchar(20),comment varchar(20)) > >> >> >> > >> >> >> go > >> >> >> > >> >> >> insert into #tmp values > >> >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) > >> >> >> > >> >> >> insert into #tmp values > >> >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) > >> >> >> > >> >> >> go > >> >> >> > >> >> >> select > >> >> >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] > >> >> >> > >> >> >> from #tmp > >> >> >> > >> >> >> pivot > >> >> >> > >> >> >> ( > >> >> >> > >> >> >> max(telno) > >> >> >> > >> >> >> for teltypes IN([Mobile],[Tellphone]) > >> >> >> > >> >> >> ) as PVT > >> >> >> > >> >> >> group by id,name,address,comment > >> >> >> > >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> >> news:1167292833.810694.305560@i12g2000cwa.googlegroups.com... > >> >> >> > Uri Dimant, > >> >> >> > > >> >> >> > Sorry,there is a mistake somewhere else.Thank you I found the > >> >> >> > answer. > >> >> >> > but it is important to using PIVOT table.Can you help me in that > >> >> >> > regard > >> >> >> > please? > >> >> >> > I am using SQL server 2005. > >> >> >> > > >> >> >> > thank you very much. > >> >> >> > Nassa > >> >> >> > > >> >> >> > Uri Dimant wrote: > >> >> >> >> Nassa > >> >> >> >> Can you show us what did you do? What is the error? > >> >> >> >> > >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message > >> >> >> >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > >> >> >> >> > Uri Dimant > >> >> >> >> > Thank you so much for your help. > >> >> >> >> > there is another question that I make a view from them and > >> >> >> >> > make > >> >> >> >> > your > >> >> >> >> > query for them but it gives me an error. > >> >> >> >> > What should I do if I want to make a query fom views? because > >> >> >> >> > it > >> >> >> >> > helps > >> >> >> >> > in query performance. > >> >> >> >> > > >> >> >> >> > Thank you for your attention. > >> >> >> >> > Nassa > >> >> >> >> > Uri Dimant wrote: > >> >> >> >> >> Nassa > >> >> >> >> >> > >> >> >> >> >>
Nassa
create table #tmp (id int,name varchar(20), telno varchar(20),telnotype int ,teltypes varchar(20),address varchar(20),comment varchar(20)) go insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) insert into #tmp values (4,'nassa','071163738',1,'Tellphone','XXX',Null) go select id,name, max(case when telnotype=1 then telno end) as 'Tellphone' , max(case when telnotype=2 then telno end) as 'Mobile', address,comment from #tmp group by id,name,address,comment [quoted text, click to view] "Nassa" <nassim.czdashti@gmail.com> wrote in message news:1167284882.174186.296770@73g2000cwn.googlegroups.com... > Hi Everyone, > > I have 3 tables: > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), > PK:id > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk > > The question is here that I can create a query which results are: > (id,Name,telno,telnotype,teltypes,address,comment) > (4,nassa,091463738,2,Mobile,XXX,Null) > (4,nassa,071163738,1,Tellphone,XXX,Nul) > > But,I want a query which shows the results in a way below: > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) > (4,nassa,071163738,091463738,Null,Null,XXX,Null) > > > Thanks, > Nassa >
Nassa (nassim.czdashti@gmail.com) writes:
[quoted text, click to view] > the error is : > The text,ntext, and image data types can not be compared or stored, > excep when using IS NULL or LIKE operator. But you were using SQL 2005, weren't you? In that case, you should seriously consider to use the nvarchar(MAX) data type rather than ntext. nvarchar(MAX) fits as much data as ntext, but does not have all the restrictions that comes with it. If you are not responsible of the data model yourself, you should convince the person who is to make a change. Life without ntext is so much easier. [quoted text, click to view] > SELECT TOP (100) PERCENT dbo.TelBook.ID, dbo.TelBook.Name, MAX(CASE >... > ORDER BY dbo.TelBook.ID Note that this TOP 100 PERCENT is completely meaningless. TOP 100 PERCENT means everything, so there is no need to specify it. In SQL 2000, if you put TOP 100 PERCENT + ORDER BY in a view, and then selected from the view without the ORDER BY, the results often came back in the same order as the ORDER BY clause in the view. This happens far less often in SQL 2005. The only way to get an ordered result from a query is to add ORDER BY to it. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Nassa
Can you show us what did you do? What is the error? [quoted text, click to view] "Nassa" <nassim.czdashti@gmail.com> wrote in message news:1167289862.043539.213500@73g2000cwn.googlegroups.com... > Uri Dimant > Thank you so much for your help. > there is another question that I make a view from them and make your > query for them but it gives me an error. > What should I do if I want to make a query fom views? because it helps > in query performance. > > Thank you for your attention. > Nassa > Uri Dimant wrote: >> Nassa >> >> >> create table #tmp (id int,name varchar(20), >> telno varchar(20),telnotype int ,teltypes varchar(20),address >> varchar(20),comment varchar(20)) >> go >> insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) >> insert into #tmp values >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) >> go >> select id,name, max(case when telnotype=1 then telno end) as 'Tellphone' >> , max(case when telnotype=2 then telno end) as 'Mobile', >> address,comment >> from #tmp >> group by id,name,address,comment >> >> >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... >> > Hi Everyone, >> > >> > I have 3 tables: >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), >> > PK:id >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk >> > >> > The question is here that I can create a query which results are: >> > (id,Name,telno,telnotype,teltypes,address,comment) >> > (4,nassa,091463738,2,Mobile,XXX,Null) >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) >> > >> > But,I want a query which shows the results in a way below: >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) >> > >> > >> > Thanks, >> > Nassa >> > >
Nassa
create table #tmp (id int,name varchar(20), telno varchar(20),telnotype int ,teltypes varchar(20),address varchar(20),comment varchar(20)) go insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) insert into #tmp values (4,'nassa','071163738',1,'Tellphone','XXX',Null) go select id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] from #tmp pivot ( max(telno) for teltypes IN([Mobile],[Tellphone]) ) as PVT group by id,name,address,comment [quoted text, click to view] "Nassa" <nassim.czdashti@gmail.com> wrote in message news:1167292833.810694.305560@i12g2000cwa.googlegroups.com... > Uri Dimant, > > Sorry,there is a mistake somewhere else.Thank you I found the answer. > but it is important to using PIVOT table.Can you help me in that regard > please? > I am using SQL server 2005. > > thank you very much. > Nassa > > Uri Dimant wrote: >> Nassa >> Can you show us what did you do? What is the error? >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... >> > Uri Dimant >> > Thank you so much for your help. >> > there is another question that I make a view from them and make your >> > query for them but it gives me an error. >> > What should I do if I want to make a query fom views? because it helps >> > in query performance. >> > >> > Thank you for your attention. >> > Nassa >> > Uri Dimant wrote: >> >> Nassa >> >> >> >> >> >> create table #tmp (id int,name varchar(20), >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address >> >> varchar(20),comment varchar(20)) >> >> go >> >> insert into #tmp values >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) >> >> insert into #tmp values >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) >> >> go >> >> select id,name, max(case when telnotype=1 then telno end) as >> >> 'Tellphone' >> >> , max(case when telnotype=2 then telno end) as 'Mobile', >> >> address,comment >> >> from #tmp >> >> group by id,name,address,comment >> >> >> >> >> >> >> >> >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... >> >> > Hi Everyone, >> >> > >> >> > I have 3 tables: >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), >> >> > PK:id >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk >> >> > >> >> > The question is here that I can create a query which results are: >> >> > (id,Name,telno,telnotype,teltypes,address,comment) >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) >> >> > >> >> > But,I want a query which shows the results in a way below: >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) >> >> > >> >> > >> >> > Thanks, >> >> > Nassa >> >> > >> > >
Nassa
Now you are seeng , instead of providing a full information about your tables (dataypes,keys) to prevent from misundertanding and additional questions you are giving us piece by piece of info about your data to make use guessing to solve the problem [quoted text, click to view] "Nassa" <nassim.czdashti@gmail.com> wrote in message news:1167294048.678344.34280@i12g2000cwa.googlegroups.com... > Uri Dimant, > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- > the error is : > The text,ntext, and image data types can not be compared or stored, > excep when using IS NULL or LIKE operator. > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- > > after I am writting this: > SELECT TOP (100) PERCENT dbo.TelBook.ID, dbo.TelBook.Name, MAX(CASE > WHEN telnotype = 1 THEN telno END) AS Tellphone, > MAX(CASE WHEN telnotype = 2 THEN telno END) AS > Mobile, MAX(CASE WHEN telnotype = 3 THEN telno END) AS Fax, > MAX(CASE WHEN telnotype = 4 THEN telno END) AS > Email,telbook.comment,telbook.address,telbook.comment > FROM dbo.TelTypes INNER JOIN > dbo.TelNumbers ON dbo.TelTypes.FK = > dbo.TelNumbers.TelNoType RIGHT OUTER JOIN > dbo.TelBook ON dbo.TelNumbers.TelBookID = > dbo.TelBook.ID > GROUP BY dbo.TelBook.ID, > dbo.TelBook.Name,telbook.comment,telbook.address,telbook.comment > ORDER BY dbo.TelBook.ID > > Thanks, > Nassa > > > Uri Dimant wrote: >> Nassa >> Can you show us what did you do? What is the error? >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... >> > Uri Dimant >> > Thank you so much for your help. >> > there is another question that I make a view from them and make your >> > query for them but it gives me an error. >> > What should I do if I want to make a query fom views? because it helps >> > in query performance. >> > >> > Thank you for your attention. >> > Nassa >> > Uri Dimant wrote: >> >> Nassa >> >> >> >> >> >> create table #tmp (id int,name varchar(20), >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address >> >> varchar(20),comment varchar(20)) >> >> go >> >> insert into #tmp values >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) >> >> insert into #tmp values >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) >> >> go >> >> select id,name, max(case when telnotype=1 then telno end) as >> >> 'Tellphone' >> >> , max(case when telnotype=2 then telno end) as 'Mobile', >> >> address,comment >> >> from #tmp >> >> group by id,name,address,comment >> >> >> >> >> >> >> >> >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... >> >> > Hi Everyone, >> >> > >> >> > I have 3 tables: >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), >> >> > PK:id >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk >> >> > >> >> > The question is here that I can create a query which results are: >> >> > (id,Name,telno,telnotype,teltypes,address,comment) >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) >> >> > >> >> > But,I want a query which shows the results in a way below: >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) >> >> > >> >> > >> >> > Thanks, >> >> > Nassa >> >> > >> > >
Nassa
Have you read this article? http://www.aspfaq.com/etiquette.asp?id=5006 [quoted text, click to view] "Nassa" <nassim.czdashti@gmail.com> wrote in message news:1167296588.452076.114110@42g2000cwt.googlegroups.com... > Uri Dimant, > I gave you all the information that anyone can need. > Which type of information do you need? > As I told you before I have had those > tables-telbook,telnumbers,teltypes-Now,I want to find a query that I > can mach with that specific result that I have shown you in the first > request massage. > > Thanks, > Nassa > > Uri Dimant wrote: >> Nassa >> Now you are seeng , instead of providing a full information about your >> tables (dataypes,keys) to prevent from misundertanding and additional >> questions >> you are giving us piece by piece of info about your data to make use >> guessing to solve the problem >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> news:1167294048.678344.34280@i12g2000cwa.googlegroups.com... >> > Uri Dimant, >> > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> > the error is : >> > The text,ntext, and image data types can not be compared or stored, >> > excep when using IS NULL or LIKE operator. >> > ---------------------------------------------------------------------------------------------------------------------------------------------------------------------- >> > >> > after I am writting this: >> > SELECT TOP (100) PERCENT dbo.TelBook.ID, dbo.TelBook.Name, MAX(CASE >> > WHEN telnotype = 1 THEN telno END) AS Tellphone, >> > MAX(CASE WHEN telnotype = 2 THEN telno END) AS >> > Mobile, MAX(CASE WHEN telnotype = 3 THEN telno END) AS Fax, >> > MAX(CASE WHEN telnotype = 4 THEN telno END) AS >> > Email,telbook.comment,telbook.address,telbook.comment >> > FROM dbo.TelTypes INNER JOIN >> > dbo.TelNumbers ON dbo.TelTypes.FK = >> > dbo.TelNumbers.TelNoType RIGHT OUTER JOIN >> > dbo.TelBook ON dbo.TelNumbers.TelBookID = >> > dbo.TelBook.ID >> > GROUP BY dbo.TelBook.ID, >> > dbo.TelBook.Name,telbook.comment,telbook.address,telbook.comment >> > ORDER BY dbo.TelBook.ID >> > >> > Thanks, >> > Nassa >> > >> > >> > Uri Dimant wrote: >> >> Nassa >> >> Can you show us what did you do? What is the error? >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... >> >> > Uri Dimant >> >> > Thank you so much for your help. >> >> > there is another question that I make a view from them and make your >> >> > query for them but it gives me an error. >> >> > What should I do if I want to make a query fom views? because it >> >> > helps >> >> > in query performance. >> >> > >> >> > Thank you for your attention. >> >> > Nassa >> >> > Uri Dimant wrote: >> >> >> Nassa >> >> >> >> >> >> >> >> >> create table #tmp (id int,name varchar(20), >> >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address >> >> >> varchar(20),comment varchar(20)) >> >> >> go >> >> >> insert into #tmp values >> >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) >> >> >> insert into #tmp values >> >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) >> >> >> go >> >> >> select id,name, max(case when telnotype=1 then telno end) as >> >> >> 'Tellphone' >> >> >> , max(case when telnotype=2 then telno end) as >> >> >> 'Mobile', >> >> >> address,comment >> >> >> from #tmp >> >> >> group by id,name,address,comment >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... >> >> >> > Hi Everyone, >> >> >> > >> >> >> > I have 3 tables: >> >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), >> >> >> > PK:id >> >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id >> >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk >> >> >> > >> >> >> > The question is here that I can create a query which results are: >> >> >> > (id,Name,telno,telnotype,teltypes,address,comment) >> >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) >> >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) >> >> >> > >> >> >> > But,I want a query which shows the results in a way below: >> >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) >> >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) >> >> >> > >> >> >> > >> >> >> > Thanks, >> >> >> > Nassa >> >> >> > >> >> > >> > >
Nassa (nassim.czdashti@gmail.com) writes:
[quoted text, click to view] > I just add order by,but when I execute the query in SQL server 2005, it > is automatically add TOP 100 Percent to the first of query.Its not my > fault. Ah, the dreadful Query Designer. Sometimes the other hand does not know what the other hand does at Microsoft. [quoted text, click to view] > by the way,the problem of that is that I cant not use ntext when I want > to compare,I dnt know where the real problem is. The problem is that you cannot do a GROUP BY on an ntext column. Did you consider changing the data type to nvarchar(MAX) as I suggested? [quoted text, click to view] > anyway,can you please help me writing a query by using pivot when I > want the specific result which I have mentioned befor? There is a new PIVOT operator in SQL 2005, and you can read about it in this place in Books Online: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/24ba54fc-98f7-4d35-8881- b5158aac1d66.htm I'm not giving any examples, because I have not bothered to learn it myself. Frankly, I find it easier to write a pivot query without it, using the method that Uri demonstrated. And I would suspect that you would have no better luck with the ntext column with PIVOT. -- Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se Books Online for SQL Server 2005 at http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx Books Online for SQL Server 2000 at
Nassa
Untested WITH PNT AS ( SELECT TB.* FROM dbo.TelBook TB INNER JOIN dbo.TelNumbers TN ON TB.ID =TN.TelBookID INNER JOIN dbo.TelTypes TT ON TT.fk=TN.telnotype ) SELECT id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] FROM PNT PIVOT ( MAX(telno) FOR teltypes IN([Mobile],[Tellphone]) ) AS PVT GROUP BY id,name,address,comment [quoted text, click to view] "Nassa" <nassim.czdashti@gmail.com> wrote in message news:1167299136.583040.19030@73g2000cwn.googlegroups.com... > Uri Dimant, > Thank you for PIVOT query, > I checked it out but it gives me an error: > ---------------------------------------------------------------------------------------------------------------------------- > The multi-part identifier "telbook.id" could not be bound. > The multi-part identifier "telbook.name" could not be bound. > The multi-part identifier "telbook.address" could not be bound. > The multi-part identifier "telbook.comment" could not be bound. > The multi-part identifier "telbook.id" could not be bound. > The multi-part identifier "telbook.name" could not be bound. > The multi-part identifier "telbook.address" could not be bound. > The multi-part identifier "telbook.comment" could not be bound. > ------------------------------------------------------------------------------------------------------------------------------- > The query is like below: > ------------------------------------------------------------------------------------------------------------------------ > SELECT > telbook.id,telbook.name,telbook.address,telbook.comment,max([telphone])[telphone],max([mobile])[mobile] > FROM dbo.TelBook INNER JOIN > dbo.TelNumbers ON dbo.TelBook.ID = > dbo.TelNumbers.TelBookID JOIN > dbo.TelTypes ON teltypes.fk=telnumbers.telnotype > PIVOT > (max(telno) > FOR teltypes IN ([telphone],[mobile]) > ) as PVT > Group By telbook.id,telbook.name,telbook.address,telbook.comment > > Uri Dimant wrote: >> Nassa >> create table #tmp (id int,name varchar(20), >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address >> >> varchar(20),comment varchar(20)) >> >> go >> >> insert into #tmp values (4,'nassa','091463738',2,'Mobile','XXX',Null) >> >> insert into #tmp values (4,'nassa','071163738',1,'Tellphone','XXX',Null) >> >> go >> >> select >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] >> >> from #tmp >> >> pivot >> >> ( >> >> max(telno) >> >> for teltypes IN([Mobile],[Tellphone]) >> >> ) as PVT >> >> group by id,name,address,comment >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> news:1167292833.810694.305560@i12g2000cwa.googlegroups.com... >> > Uri Dimant, >> > >> > Sorry,there is a mistake somewhere else.Thank you I found the answer. >> > but it is important to using PIVOT table.Can you help me in that regard >> > please? >> > I am using SQL server 2005. >> > >> > thank you very much. >> > Nassa >> > >> > Uri Dimant wrote: >> >> Nassa >> >> Can you show us what did you do? What is the error? >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> >> news:1167289862.043539.213500@73g2000cwn.googlegroups.com... >> >> > Uri Dimant >> >> > Thank you so much for your help. >> >> > there is another question that I make a view from them and make your >> >> > query for them but it gives me an error. >> >> > What should I do if I want to make a query fom views? because it >> >> > helps >> >> > in query performance. >> >> > >> >> > Thank you for your attention. >> >> > Nassa >> >> > Uri Dimant wrote: >> >> >> Nassa >> >> >> >> >> >> >> >> >> create table #tmp (id int,name varchar(20), >> >> >> telno varchar(20),telnotype int ,teltypes varchar(20),address >> >> >> varchar(20),comment varchar(20)) >> >> >> go >> >> >> insert into #tmp values >> >> >> (4,'nassa','091463738',2,'Mobile','XXX',Null) >> >> >> insert into #tmp values >> >> >> (4,'nassa','071163738',1,'Tellphone','XXX',Null) >> >> >> go >> >> >> select id,name, max(case when telnotype=1 then telno end) as >> >> >> 'Tellphone' >> >> >> , max(case when telnotype=2 then telno end) as >> >> >> 'Mobile', >> >> >> address,comment >> >> >> from #tmp >> >> >> group by id,name,address,comment >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> >> >> news:1167284882.174186.296770@73g2000cwn.googlegroups.com... >> >> >> > Hi Everyone, >> >> >> > >> >> >> > I have 3 tables: >> >> >> > Telbook(Id:int,Name:char,address:char,comment:ntext,owneruserid:int), >> >> >> > PK:id >> >> >> > TelNumbers(telbookid:int,telno:char,telNotype:int,syscode:int),PK:syscode,FK:telNumbers.telbookid=telbook.id >> >> >> > Teltypes(teltypes:char,fk:int),FK:telnumbers.telnotype=teltypes.fk >> >> >> > >> >> >> > The question is here that I can create a query which results are: >> >> >> > (id,Name,telno,telnotype,teltypes,address,comment) >> >> >> > (4,nassa,091463738,2,Mobile,XXX,Null) >> >> >> > (4,nassa,071163738,1,Tellphone,XXX,Nul) >> >> >> > >> >> >> > But,I want a query which shows the results in a way below: >> >> >> > (id,Name,tellephone,mobile,Fax,e-mail,address,comment) >> >> >> > (4,nassa,071163738,091463738,Null,Null,XXX,Null) >> >> >> > >> >> >> > >> >> >> > Thanks, >> >> >> > Nassa >> >> >> > >> >> > >> > >
CTE-Common Table Expression. It was introduced in SQL Server 2005. [quoted text, click to view] "Nassa" <nassim.czdashti@gmail.com> wrote in message news:1167300532.067806.254930@79g2000cws.googlegroups.com... > Uri Dimant, > > What does "With PNT" mean? > > Thanks, > Nassa > > Uri Dimant wrote: >> Nassa >> Untested >> >> WITH PNT >> >> AS >> >> ( >> >> SELECT TB.* >> >> FROM dbo.TelBook TB INNER JOIN >> >> dbo.TelNumbers TN ON TB.ID =TN.TelBookID >> >> INNER JOIN dbo.TelTypes TT ON TT.fk=TN.telnotype >> >> ) >> >> SELECT >> id,name,address,comment,max([Mobile])[Mobile],max([Tellphone])[Tellphone] >> >> FROM PNT >> >> PIVOT >> >> ( >> >> MAX(telno) >> >> FOR teltypes IN([Mobile],[Tellphone]) >> >> ) AS PVT >> >> GROUP BY id,name,address,comment >> >> >> >> >> >> >> >> >> >> "Nassa" <nassim.czdashti@gmail.com> wrote in message >> news:1167299136.583040.19030@73g2000cwn.googlegroups.com... >> > Uri Dimant, >> > Thank you for PIVOT query, >> > I checked it out but it gives me an error: >> > ---------------------------------------------------------------------------------------------------------------------------- >> > The multi-part identifier "telbook.id" could not be bound. >> > The multi-part identifier "telbook.name" could not be bound. >> > The multi-part identifier "telbook.address" could not be bound. >> > The multi-part identifier "telbook.comment" could not be bound. >> > The multi-part identifier "telbook.id" could not be bound. >> > The multi-part identifier "telbook.name" could not be bound. >> > The multi-part identifier "telbook.address" could not be bound. >> > The multi-part identifier "telbook.comment" could not be bound. >> > ------------------------------------------------------------------------------------------------------------------------------- >> > The query is like below: >> > ------------------------------------------------------------------------------------------------------------------------ >> > SELECT >> > telbook.id,telbook.name,telbook.address,telbook.comment,max([telphone])[telphone],max([mobile])[mobile] >> > FROM dbo.TelBook INNER JOIN >> > dbo.TelNumbers ON dbo.TelBook.ID = >> > dbo.TelNumbers.TelBookID JOIN >> > dbo.TelTypes ON teltypes.fk=telnumbers.telnotype >> > PIVOT >> > (max(telno) >> > FOR teltypes IN ([telphone],[mobile]) >> > ) as PVT >> > Group By telbook.id,telbook.name,telbook.address,telbook.comment< |