CODEWITHSUNDEEP
sqlsql-server-2008pivotpivot-table
Jonas T
Jonas T

Reputation: 3077

T-SQL PIVOT: One row to column name and the other to value

I am using SQL Server 2008.

I have two tables

  1. User (UserID, Name, Link)
  2. 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 SQL Fiddle

I want to create 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 child table should become column name and Description should become value of that column and we do not know how many rows we will have. I am aware of the issue when we select two users and which name to use column name. In that case we can use (Note1, Note2, Note3, etc for multiple User), otherwise use title field as Column name. Is it possible to do so? Cheers!

Upvotes: 5

Views: 4885

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24076

this is not a view.. This is a procedure which will return the result as you want

you can call it as

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

Upvotes: 1

Oleksandr Fedorenko
Oleksandr Fedorenko

Reputation: 16904

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

Upvotes: 2

Related Questions