Pivot while changing data types (dynamically)

Question

Let's say I have 3 tables: users, customattributes, and customattributevalues. End user can add custom attributes by selecting a name and type for the attribute, and the edit the values for any user.

Here are my users:

id	firstname	lastname	active	datecreated	username	email
3	Ellen	Ripley	1	3/25/2235	78439	Rip8439@Weyland-Yutani.com
5	Johnny	Rico	1	4/16/2675	Roughneck31	RicoJ31@Roughnecks.com

customattributes (can be added to anytime)

id	fullname	uniquename	type
1	Hire Date	hiredate	date
2	Employee ID	eeid	int
3	Supervisor	supervisor	nvarchar(50)
4	Assigned Ship	assignedship	nvarchar(50)
5	Job Title	jobtitle	nvarchar(50)

type I currently have as sysname datatype.

customattributevalues (can be edited anytime)

id	attributeid	userid	value
1	1	3	2335-03-25
2	2	3	78439
3	3	3	Burke, Carter
4	4	3	Sulaco
5	5	3	Consultant
6	1	5	2675-04-16
7	2	5	78440
8	3	5	LT Rasczak
9	4	5	Rodger Young
10	5	5	Private

value I currently have as sql_variant datatype

So here is my question: how can I create a report that shows all employees and their attributes, 1 line per employee, without knowing how many custom attributes there are --and-- crucially, I want to explicitly convert each column to the correct data type

Desired output:

firstname	lastname	datecreated	username	email	Hire Date	Employee ID	Supervisor	Assigned Ship	Job Title
Ellen	Ripley	2235-03-25	78439	Rip8439@Weyland-Yutani.com	2335-03-25	78439	Burke, Carter	Sulaco	Consultant
Johnnie	Rico	2675-04-16	Roughneck31	RicoJ31@Roughnecks.com	2675-04-16	78440	LT Rasczak	Rodger Young	Private

I've already learned to do the dynamic column headers using dynamic queries, but it is the type conversion that is escaping me.

I'm adapting this solution for custom fields, but the limitation to this solution is you have to know each custom field to make the type conversion.

Here is what I've tried. I got the correct output, except for the type conversions.

Query:

DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sqlcmd NVARCHAR(MAX) = '';

SELECT @columns += QUOTENAME(fullname) + ','
FROM customattributesx ca

ORDER BY ca.id;

SET @columns = LEFT(@columns, LEN(@columns) - 1);

--PRINT @columns;

SET @sqlcmd = '
SELECT * FROM (
    SELECT userid
        ,firstname
        ,lastname
        ,datecreated
        ,username
        ,email
        ,fullname
        ,value
    FROM (
        SELECT u.id as userid
              ,u.firstname
              ,u.lastname
              ,u.datecreated
              ,u.username
              ,u.email
              ,ca.id
              ,ca.fullname as fullname
              ,ca.uniquename
              ,ca.type
              ,cav.value as value
        FROM dbo.users u
        CROSS JOIN customattributesx ca
        INNER JOIN customattributevaluesx cav
            ON cav.attributeid = ca.id AND cav.userid = u.id

        --ORDER BY u.id asc, ca.id asc
    ) t1
) t2
PIVOT (
    MIN(value)
    FOR fullname IN ('+@columns+')
) as pivottable
';
--print @sqlcmd
EXECUTE (@sqlcmd)

Create Tables:

USE [CTMS]
GO

/****** Object:  Table [dbo].[users]    Script Date: 11/24/2021 9:29:16 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[users](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [firstname] [nvarchar](max) NULL,
    [lastname] [nvarchar](max) NULL,
    [active] [bit] NOT NULL,
    [datecreated] [datetime2](7) NOT NULL,
    [username] [nvarchar](256) NULL,
    [email] [nvarchar](256) NULL,
    [emailconfirmed] [bit] NOT NULL,
    [passwordhash] [nvarchar](max) NULL,
    [twofactorenabled] [bit] NOT NULL,
    [lockoutend] [datetimeoffset](7) NULL,
    [eockoutenabled] [bit] NOT NULL,
    [accessfailedcount] [int] NOT NULL,
    [qrcode] [nvarchar](50) NULL,
 CONSTRAINT [PK_id] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [uk_email] UNIQUE NONCLUSTERED 
(
    [email] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [uk_qrcode] UNIQUE NONCLUSTERED 
(
    [qrcode] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [uk_username] UNIQUE NONCLUSTERED 
(
    [username] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[users] ADD  DEFAULT (getutcdate()) FOR [datecreated]
GO


USE [CTMS]
GO

/****** Object:  Table [dbo].[customattributesx]    Script Date: 11/24/2021 9:31:09 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[customattributesx](
    [id] [smallint] IDENTITY(1,1) NOT NULL,
    [fullname] [nvarchar](50) NOT NULL,
    [uniquename] [nvarchar](50) NOT NULL,
    [type] [sysname] NOT NULL,
 CONSTRAINT [PK_customattributesx] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [uk1_customattributesx] UNIQUE NONCLUSTERED 
(
    [uniquename] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

USE [CTMS]
GO

/****** Object:  Table [dbo].[customattributevaluesx]    Script Date: 11/24/2021 9:31:27 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[customattributevaluesx](
    [id] [int] IDENTITY(1,1) NOT NULL,
    [attributeid] [smallint] NOT NULL,
    [userid] [int] NOT NULL,
    [value] [sql_variant] NOT NULL,
 CONSTRAINT [PK_customattributevaluesx] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
 CONSTRAINT [uk1_customattributevaluesx] UNIQUE NONCLUSTERED 
(
    [attributeid] ASC,
    [userid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[customattributevaluesx]  WITH CHECK ADD  CONSTRAINT [fk1_customattributesvaluesx] FOREIGN KEY([attributeid])
REFERENCES [dbo].[customattributesx] ([id])
GO

ALTER TABLE [dbo].[customattributevaluesx] CHECK CONSTRAINT [fk1_customattributesvaluesx]
GO

ALTER TABLE [dbo].[customattributevaluesx]  WITH CHECK ADD  CONSTRAINT [fk2_customattributesvaluesx] FOREIGN KEY([userid])
REFERENCES [dbo].[users] ([id])
GO

ALTER TABLE [dbo].[customattributevaluesx] CHECK CONSTRAINT [fk2_customattributesvaluesx]
GO

Answer 1

If you must convert the datatype (could really be a presentation layer thing), then a dynamic conditional aggregation should do the trick.

Example

Declare @SQL nvarchar(max) ='
Select U.*' +
(
Select concat(',',quotename(fullname),'=max(case when attributeid=',id,' then try_convert(',type,',value) end)')
 From customattributes
 For XML Path ('')
)+'
 From  users U
 Join  customattributesvalues V on U.ID=V.userid
 Group By U.ID
         ,U.FirstName
         ,U.LastName
         ,U.active
         ,U.datecreated
         ,U.username
         ,U.email
'
--print @SQL
Exec(@SQL)

Results

The Generated SQL Looks Like This

Select U.*
      ,[Hire Date]=max(case when attributeid=1 then try_convert(date,value) end)
      ,[Employee ID]=max(case when attributeid=2 then try_convert(int,value) end)
      ,[Supervisor]=max(case when attributeid=3 then try_convert(nvarchar(50),value) end)
      ,[Assigned Ship]=max(case when attributeid=4 then try_convert(nvarchar(50),value) end)
      ,[Job Title]=max(case when attributeid=5 then try_convert(nvarchar(50),value) end)
 From  #users U
 Join  #customattributesvalues V on U.ID=V.userid
 Group By U.ID
         ,U.FirstName
         ,U.LastName
         ,U.active
         ,U.datecreated
         ,U.username
         ,U.email

Answer 2

SQL_VARIANT can be cast to a destination data type.

Modify part of the dynamic query where you generate column list, to generate two lists. One list is for PIVOT part and the other for SELECT part where you cast you data types.

Example is based on the article you refer to in your question:

DECLARE @PivotList NVARCHAR( MAX )
DECLARE @SelectList NVARCHAR( MAX )
SELECT @SelectList = NULL, @PivotList = NULL
        -- Column list with CAST e.g. CAST( eeid AS INT ) AS eeid
        -- Data types come from your customattributes table
SELECT @SelectList = COALESCE( @SelectList + ',','') + 'CAST( ' + uniquename + ' AS [type] ) AS ' + uniquename,
        -- Just a column list that goes into PIVOT operator
        @PivotList = COALESCE( @PivotList + ',','') + uniquename
-- Your tables for attribute values and attribute type definitions
FROM customattributes AS ca

DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery =

'SELECT StudID , '+@SelectList+'
FROM
( SELECT SM.StudID, S.SubjectName, SM.Score 
FROM StudentMarks SM 
INNER JOIN Subjects S
ON Sm.SubjectID = S.SubjectID
) AS tbl
PIVOT 
( Sum(Score)
FOR SubjectName IN ('+@PivotList+') ) as pvt'

EXEC(@SQLQuery)