Transpose table and save as View

Question

I have a dynamic pivot/unpivot script that transposes a table. This is dynamic enough to return certain columns that I want and using dynamic columns.

What I am looking for is rather to convert this into either a UDF or a VIEW so that I can join it to other tables.

Please help.

ALTER PROC [dbo].[uspGetUserByValues]
(
    @Select NVARCHAR(4000) = '*',
    @Where NVARCHAR(4000) = NULL,
    @OrderBy NVARCHAR(4000) = NULL
)
AS
BEGIN
    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',P.' + QUOTENAME(PropertyDescription) 
                    from System_Properties
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

    set @query = 'SELECT ' + @cols + ', M.Email, C.Company_Name, C.Company_Type_ID, U.UserName, ISNULL(SMS.SMSProfiles,0) SMSProfiles, U.UserID
                 from 
                 (
                    select PropertyDescription, UP.UserID, PropertyValue
                    from User_Properties UP 
                        JOIN System_Properties SP ON UP.PropertyID = SP.PropertyID
                        JOIN aspnet_Membership M ON UP.UserID = M.UserID
                ) X 
                pivot 
                (
                    min(PropertyValue)
                    for PropertyDescription in (' + REPLACE(@cols,'P.','') + ')
                ) P 
                JOIN aspnet_Membership M ON P.UserID = M.UserID
                JOIN aspnet_Users U on P.UserID = U.UserID
                JOIN Companies C ON C.Company_ID = P.Company_ID
                LEFT JOIN (SELECT UserId, COUNT(Users_SMS_Profile_ID) SMSProfiles 
                        FROM Users_SMS_Profile GROUP BY UserID ) SMS ON SMS.UserID = P.UserID
                '

    SET @query = 'SELECT ' + @Select + ' FROM ('+ @query +') A'

    IF ISNULL(@Where,'NULL') != 'NULL'
    BEGIN
        SET @query = @query + ' WHERE ' + @Where
    END
    IF ISNULL(@OrderBy,'NULL') != 'NULL'
    BEGIN
        SET @query = @query + ' ORDER BY ' + @OrderBy
    END

    execute(@query)

    --PRINT(@query)
END

Answer 1

OH wow I made it.

I know this is with "known" column names but actually I didn't have to know them.

Firstly, this is the query I used to create the View. I will need to drop the view at least every I add a new Property or I can actually write a job that checks if all the properties from System_Properties are represented in the view, if not then drop the view and run this code.

CREATE PROC [dbo].[uspCreateViewUsers]
AS
BEGIN
    DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',P.' + QUOTENAME(PropertyDescription) 
                    from System_Properties
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

    set @query = 'CREATE VIEW vwUsers AS SELECT ' + @cols + ', M.Email, C.Company_Name, C.Company_Type_ID, U.UserName, ISNULL(SMS.SMSProfiles,0) SMSProfiles, U.UserID
                 from 
                 (
                    select PropertyDescription, UP.UserID, PropertyValue
                    from User_Properties UP 
                        JOIN System_Properties SP ON UP.PropertyID = SP.PropertyID
                        JOIN aspnet_Membership M ON UP.UserID = M.UserID
                ) X 
                pivot 
                (
                    min(PropertyValue)
                    for PropertyDescription in (' + REPLACE(@cols,'P.','') + ')
                ) P 
                JOIN aspnet_Membership M ON P.UserID = M.UserID
                JOIN aspnet_Users U on P.UserID = U.UserID
                JOIN Companies C ON C.Company_ID = P.Company_ID
                LEFT JOIN (SELECT UserId, COUNT(Users_SMS_Profile_ID) SMSProfiles 
                        FROM Users_SMS_Profile GROUP BY UserID ) SMS ON SMS.UserID = P.UserID
                '

    execute(@query)
END

Them the View, which can't be represented graphically by table joins looks like this:

SELECT     P.[Company_ID], P.[Created_Date], P.[Created_User], P.[Cust_ID], P.[FirstName], P.[IPCheck], P.[JobTitle], P.[LastLogin], P.[LastModified_Date], P.[LastModified_User], 
                      P.[LastName], P.[Newsletter_OptIn], P.[Password_Change], P.[SupAdmin], P.[SysAccess], P.[SysAdmin], P.[User_Cat_1], P.[User_Cat_10], P.[User_Cat_2], 
                      P.[User_Cat_3], P.[User_Cat_4], P.[User_Cat_5], P.[User_Cat_6], P.[User_Cat_7], P.[User_Cat_8], P.[User_Cat_9], P.[UserClient_ID], M.Email, C.Company_Name, 
                      C.Company_Type_ID, U.UserName, ISNULL(SMS.SMSProfiles, 0) SMSProfiles, U.UserID
FROM         (SELECT     PropertyDescription, UP.UserID, PropertyValue
                       FROM          User_Properties UP JOIN
                                              System_Properties SP ON UP.PropertyID = SP.PropertyID JOIN
                                              aspnet_Membership M ON UP.UserID = M.UserID) X PIVOT (min(PropertyValue) FOR PropertyDescription IN ([Company_ID], [Created_Date], [Created_User], 
                      [Cust_ID], [FirstName], [IPCheck], [JobTitle], [LastLogin], [LastModified_Date], [LastModified_User], [LastName], [Newsletter_OptIn], [Password_Change], [SupAdmin], 
                      [SysAccess], [SysAdmin], [User_Cat_1], [User_Cat_10], [User_Cat_2], [User_Cat_3], [User_Cat_4], [User_Cat_5], [User_Cat_6], [User_Cat_7], [User_Cat_8], 
                      [User_Cat_9], [UserClient_ID])) P JOIN
                      aspnet_Membership M ON P.UserID = M.UserID JOIN
                      aspnet_Users U ON P.UserID = U.UserID JOIN
                      Companies C ON C.Company_ID = P.Company_ID LEFT JOIN
                          (SELECT     UserId, COUNT(Users_SMS_Profile_ID) SMSProfiles
                            FROM          Users_SMS_Profile
                            GROUP BY UserID) SMS ON SMS.UserID = P.UserID

This now allows me to query the View as if it was a table.

I hope this helps someone else in the future.