Concat tables into one row in view

If I have table1 and table2 with table2 having multiple rows tied to a
single row in table 1.

What I am trying to do is set up a view that has one row that shows
the following
table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
table2.row3.detail

I'd like to be able to do a select on the view and only come back with
one row per widget. If possible, I'd actually like to be able to
concat all the rows from table 2 into one column if that's possible.

table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
- table2.row3.detail), table1.dateCreated

thx
M@

Re: Concat tables into one row in view

M@ (mattcushing@gmail.com) writes:
> If I have table1 and table2 with table2 having multiple rows tied to a
> single row in table 1.
>
> What I am trying to do is set up a view that has one row that shows
> the following
> table1.uniqueid, table1.name, table2.row1.detail, table2.row2.detail,
> table2.row3.detail
>
> I'd like to be able to do a select on the view and only come back with
> one row per widget. If possible, I'd actually like to be able to
> concat all the rows from table 2 into one column if that's possible.
>
> table1.uniqueid, table1.name, (table2.row1.detail - table2.row2.detail
> - table2.row3.detail), table1.dateCreated

SQL Server MVP Anith Sen has a couple of methods on
http://www.projectdmx.com/tsql/rowconcatenate.aspx.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: Concat tables into one row in view

found a really good alternative on sqlteam.com

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

Re: Concat tables into one row in view

M@ (mattcushing@gmail.com) writes:
> found a really good alternative on sqlteam.com
>
> 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)

Beware! The correct result of this operation is not defined! You may get
what you want, you may get something else! It works most of the time,
but there is no guarantee!




--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx