Stored procedure with Dynamic columns?

[Softmaker]

am making a CMS solution and need help to get out an article list. The reason why this is a bit tricky is because articles does not have the same fields stored in one static article-table. In tblArticle all standard information about each article is stored. In tblArticleAttribute all different kind on information for an article is stored, defines by the templateDefinition table.

Expample: Template 1 has Title, ingress, body). In tblArticleAttribute all content is stored, for example Title Text, Ingress Text and Body text for ArticleId1 and TemplateDefinitionId 1.

I have made a View that returns 2 articles like this:

SELECT     dbo.tblArticle.Id, dbo.tblArticle.PublishStart, dbo.tblArticle.Name AS ArticleName, dbo.tblTemplate.Id AS TemplateId, 
                      dbo.tblTemplate.Name AS TemplateName, dbo.tblTemplateDefinition.Name AS AttributeName, dbo.tblArticleAttribute.[Content] AS AttributeValue
FROM         dbo.tblArticle INNER JOIN
                      dbo.tblTemplate ON dbo.tblArticle.TemplateId = dbo.tblTemplate.Id INNER JOIN
                      dbo.tblTemplateDefinition ON dbo.tblTemplate.Id = dbo.tblTemplateDefinition.TemplateId INNER JOIN
                      dbo.tblArticleAttribute ON dbo.tblTemplateDefinition.Id = dbo.tblArticleAttribute.TemplateDefinitionId AND 
                      dbo.tblArticle.Id = dbo.tblArticleAttribute.ArticleId
WHERE     (dbo.tblArticle.ParentId = 3) AND (dbo.tblTemplate.Id = 1)

The View return 2 articles:

ID | Publish start | ArticleName | TemplateID | TemplateName | AttributeName| AttributeValue
4	22.05.2007	Article1	1		Article		Title		Welcome
4	22.05.2007	Article1	1		Article		Ingress		Hello
4	22.05.2007	Article1	1		Article		Body		Cool
4	22.05.2007	Article1	1		Article		Info		Yeah..
5	26.06.2007	Article2	1		Article		Title		ByeBye
5	26.06.2007	Article2	1		Article		Ingress		Ughhh
5	26.06.2007	Article2	1		Article		Body		Nice
5	26.06.2007	Article2	1		Article		Info		Arrrg

As you see it return 4 rows per article. This is because the Article template has 4 templateDefinitions. Another template can have more or less templatedefinitions. Is it possible to make a query based on this View that return 1 record for each article? Then each AttributeName shall be a column name, and the attributeValue shall be the value for this Column.

This is really hard to make, ugh!!!

[sibir1us]

There are couple things:

1. Read some article on database normalization and revisit your database model

2. don't use views if you can. Views are in general a bad idea in SQL Server.