|
|
You're in the
sql server (alternate)
group:Transposing rows to columns
sql server (alternate):
[quoted text, click to view] "SakyMoto" <jeroen.tijsen@gmail.com> wrote in message news:1154344089.525844.305540@s13g2000cwa.googlegroups.com... > I hope someone can help me with my problem. I have searched the > internet for days for a solution, but nothing i found seemed to work. > The following is what i have now: What version of SQL are you using? SQL 2005 has a PIVOT commandbuilt in. Otherwise you can google for some code examples of how to do it in SQL 2000 (it ain't overly pretty.) [quoted text, click to view] > > appartmentid code > 100306 Wip > 100306 Zandbak > 100306 Glijbaan > 100306 Klimrek > 100306 Schommel > 100321 Glijbaan > 100321 Schommel > > This results are made with this query: > > select > appartment.appartmentid, > listvalue.code > from appartment > inner join appartmentlistvalue on appartmentlistvalue.appartmentid = > appartment.appartmentid > inner join listvalue on appartmentlistvalue.listvalueid = > listvalue.listvalueid > > The following is what i need: > > 100306 Wip, Zandbak, Glijbaan, Klimrek, Schommel > 100312 Glijbaan, Schommel > > As you can see is this example, not all appartments have the same > number of results. Can anyone tell me if this is possible? > > Many thanks, > Sakymoto >
I hope someone can help me with my problem. I have searched the
internet for days for a solution, but nothing i found seemed to work. The following is what i have now: appartmentid code 100306 Wip 100306 Zandbak 100306 Glijbaan 100306 Klimrek 100306 Schommel 100321 Glijbaan 100321 Schommel This results are made with this query: select appartment.appartmentid, listvalue.code from appartment inner join appartmentlistvalue on appartmentlistvalue.appartmentid = appartment.appartmentid inner join listvalue on appartmentlistvalue.listvalueid = listvalue.listvalueid The following is what i need: 100306 Wip, Zandbak, Glijbaan, Klimrek, Schommel 100312 Glijbaan, Schommel As you can see is this example, not all appartments have the same number of results. Can anyone tell me if this is possible? Many thanks, Sakymoto
SakyMoto (jeroen.tijsen@gmail.com) writes:
[quoted text, click to view] > I hope someone can help me with my problem. I have searched the > internet for days for a solution, but nothing i found seemed to work. > The following is what i have now: > > appartmentid code > 100306 Wip > 100306 Zandbak > 100306 Glijbaan > 100306 Klimrek > 100306 Schommel > 100321 Glijbaan > 100321 Schommel > > This results are made with this query: > > select > appartment.appartmentid, > listvalue.code > from appartment > inner join appartmentlistvalue on appartmentlistvalue.appartmentid = > appartment.appartmentid > inner join listvalue on appartmentlistvalue.listvalueid = > listvalue.listvalueid > > The following is what i need: > > 100306 Wip, Zandbak, Glijbaan, Klimrek, Schommel > 100312 Glijbaan, Schommel > > As you can see is this example, not all appartments have the same > number of results. Can anyone tell me if this is possible? On SQL 2005 you can use: SELECT a.appartmentid, substring(x.CodeList, 1, len(CodeList) - 1) FROM appartment a CROSS APPLY (select l.code + ',' AS [text()] from appartmentlistvalue alv ond join listvalue l on alv.listvalueid = l.listvalueid WHERE alv.appartmentid = a.appartmenti ORDER BY a.appartmentid FOR XML PATH ('')) AS x(CodeList) The syntax is a bit obscure, but it works. On SQL 2000, you cannot do this in a single SQL statement, but you need to run a cursor to build the lists. You may be better off to compose the lists in the client layer. -- 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
Erland Sommarskog schreef: [quoted text, click to view] > SakyMoto (jeroen.tijsen@gmail.com) writes: > > I hope someone can help me with my problem. I have searched the > > internet for days for a solution, but nothing i found seemed to work. > > The following is what i have now: > > > > appartmentid code > > 100306 Wip > > 100306 Zandbak > > 100306 Glijbaan > > 100306 Klimrek > > 100306 Schommel > > 100321 Glijbaan > > 100321 Schommel > > > > This results are made with this query: > > > > select > > appartment.appartmentid, > > listvalue.code > > from appartment > > inner join appartmentlistvalue on appartmentlistvalue.appartmentid = > > appartment.appartmentid > > inner join listvalue on appartmentlistvalue.listvalueid = > > listvalue.listvalueid > > > > The following is what i need: > > > > 100306 Wip, Zandbak, Glijbaan, Klimrek, Schommel > > 100312 Glijbaan, Schommel > > > > As you can see is this example, not all appartments have the same > > number of results. Can anyone tell me if this is possible? > > On SQL 2005 you can use: > > SELECT a.appartmentid, > substring(x.CodeList, 1, len(CodeList) - 1) > FROM appartment a > CROSS APPLY (select l.code + ',' AS [text()] > from appartmentlistvalue alv ond > join listvalue l on alv.listvalueid = l.listvalueid > WHERE alv.appartmentid = a.appartmenti > ORDER BY a.appartmentid > FOR XML PATH ('')) AS x(CodeList) > > The syntax is a bit obscure, but it works. > > On SQL 2000, you cannot do this in a single SQL statement, but you need to > run a cursor to build the lists. You may be better off to compose the > lists in the client layer. > > > -- > 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 It's a shame but we haven't yet migrated to SQL server 2005. I will try to accomplish the result with a cursor
Hi,
Here is an example that may meet your needs. It's a bit rough but it does work. USE Northwind GO CREATE FUNCTION dbo.ConcatOrderProducts(@OrderID int) RETURNS VARCHAR(8000) AS BEGIN DECLARE @Output VARCHAR(8000) SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), P.ProductName) FROM dbo.[Order Details] OD JOIN dbo.Products P ON P.ProductID = OD.ProductID WHERE OD.OrderID = @OrderID ORDER BY P.ProductName RETURN @Output END GO SELECT OrderID, CustomerID, dbo.ConcatOrderProducts(OrderID) FROM Orders GO DROP FUNCTION dbo.ConcatOrderProducts GO thanks Bryan [quoted text, click to view] SakyMoto wrote:
> I hope someone can help me with my problem. I have searched the > internet for days for a solution, but nothing i found seemed to work. > The following is what i have now: > > appartmentid code > 100306 Wip > 100306 Zandbak > 100306 Glijbaan > 100306 Klimrek > 100306 Schommel > 100321 Glijbaan > 100321 Schommel > > This results are made with this query: > > select > appartment.appartmentid, > listvalue.code > from appartment > inner join appartmentlistvalue on appartmentlistvalue.appartmentid = > appartment.appartmentid > inner join listvalue on appartmentlistvalue.listvalueid = > listvalue.listvalueid > > The following is what i need: > > 100306 Wip, Zandbak, Glijbaan, Klimrek, Schommel > 100312 Glijbaan, Schommel > > As you can see is this example, not all appartments have the same > number of results. Can anyone tell me if this is possible? > > Many thanks, > Sakymoto
Bryan (bryanmcguire@btinternet.com) writes:
[quoted text, click to view] > Here is an example that may meet your needs. It's a bit rough but it > does work. > > > USE Northwind > GO > CREATE FUNCTION dbo.ConcatOrderProducts(@OrderID int) > RETURNS VARCHAR(8000) > AS > BEGIN > DECLARE @Output VARCHAR(8000) > SELECT @Output = COALESCE(@Output+', ', '') + CONVERT(varchar(20), > P.ProductName) > FROM dbo.[Order Details] OD > JOIN dbo.Products P > ON P.ProductID = OD.ProductID > WHERE OD.OrderID = @OrderID > ORDER BY P.ProductName Nah, it seems to work, but there is not really any guarantee. It relies on undefined behaviour, and you may get the result you expect. -- 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
Don't see what you're looking for? Try a search.
|
January 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
| home · blog · groups | Questions? Comments? Contact the dn |