T-SQL PIVOT: one row for the column name and another for the value

Question

I am using SQL Server 2008.

I have two tables

• User ( UserID, Name, Link )
• UserNotes ( NoteID, UserID, Title, Description )

This is the sample data structure.

 INSERT INTO [User] ([UserID], [Name], [Link]) VALUES (1, 'John', 'L1'), (2, 'Steve', 'L234'); INSERT INTO [UserNotes] ([NoteID], [UserID], [Title], [Description]) VALUES (1, 1, 'AboutJohn', 'This is about john'), (2, 1, 'John Work', 'This is where John work'), (3, 1, 'John Education', 'This is the uni where John go'), (4, 2, 'Steve Note1', 'Des1 about Steve'), (5, 2, 'Steve Note2', 'Des2 about Steve'); 

Here is the SQL Fiddle

I want to create a view ( User_view ) as follows, and when I execute this command, the output should be as follows.

 SELECT * FROM User_view WHERE UserID IN (1) UserID Name AboutJOhn JohnWork JohnEducation 1 John This is about john This is where Johnwork This is the uni where John go 

Title column of the child table should become the name of the column, and Description should become the value of this column, and we do not know how many rows we will have. I know about this problem when we select two users and which name uses the column name. In this case, we can use (Note1, Note2, Note3, etc. For multiple users), otherwise use the header field as the column name. Can this be done? Hooray!

Answer 1

this is not a representation. This is a procedure that will return the result as you want.

you can call him

proc_UserNotes 1
proc_UserNotes 2

etc.

 CREATE procedure proc_UserNotes (@UserID int) as begin DECLARE @cols AS NVARCHAR(MAX), @cols_WITH_MAX AS NVARCHAR(MAX), @query AS NVARCHAR(MAX); select @cols_WITH_MAX = STUFF((SELECT distinct ',MAX(' + QUOTENAME(Title) +') AS ' + QUOTENAME(Title) from [UserNotes] where UserID =@UserID FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Title) from [UserNotes] where UserID =@UserID FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT [UserID], '+ @cols_WITH_MAX +' FROM( SELECT [UserID], ' + @cols + ' from ( SELECT * FROM [UserNotes] where UserID ='+cast(@UserID as varchar(20)) +' )X pivot ( MAX([Description]) for [Title] in (' + @cols + ') ) p )a GROUP BY [UserID]' print(@query) execute(@query) end 

Answer 2

 CREATE VIEW User_view AS SELECT UserID, Name, [AboutJohn], [John Work], [John Education] FROM ( SELECT n.UserID, u.Name, n.Title, n.Description FROM [User] u JOIN UserNotes n ON u.UserID = n.UserID WHERE u.UserID IN (1) ) a PIVOT ( MAX(Description) FOR Title IN ([AboutJohn], [John Work], [John Education]) ) b