CODEWITHSUNDEEP
sql-server-2008-r2
alexandrovdi
alexandrovdi

Reputation: 301

How can I do Select like this

    Declare @Result as varchar(max)='';
SELECT DISTINCT @Result =  dbo.MaterialTypes.Title + ', '+ @Result
FROM         dbo.TempId LEFT OUTER JOIN
                      dbo.ProductMaterials ON dbo.ProductMaterials.Product = dbo.TempId.Id LEFT OUTER JOIN
                      dbo.MaterialTypes ON dbo.ProductMaterials.MaterialType = dbo.MaterialTypes.Id LEFT OUTER JOIN
                      dbo.Products ON dbo.Products.Id = dbo.TempId.Id
WHERE     (dbo.Products.IsCollection = 1)



SELECT DISTINCT dbo.TempId.Id AS MaterialCollection_id, @Result
FROM         dbo.TempId LEFT OUTER JOIN
                      dbo.ProductMaterials ON dbo.ProductMaterials.Product = dbo.TempId.Id LEFT OUTER JOIN
                      dbo.MaterialTypes ON dbo.ProductMaterials.MaterialType = dbo.MaterialTypes.Id LEFT OUTER JOIN
                      dbo.Products ON dbo.Products.Id = dbo.TempId.Id
WHERE     (dbo.Products.IsCollection = 1)

this query is work, but i can't create View, based on this query. Please help!

Upvotes: 0

Views: 109

Answers (1)

Özgür Kara
Özgür Kara

Reputation: 1333

As murtaza says you can create a Stored Procedure for this, or if you want to use this as a view you can create a Table-valued Function which you can use other views and joins.

http://msdn.microsoft.com/en-us/library/ms191165.aspx

Upvotes: 1

Related Questions