Pivot while changing data types (dynamically)-CodePudding

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	[email protected]
5	Johnny	Rico	1	4/16/2675	Roughneck31	[email protected]

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	[email protected]	2335-03-25	78439	Burke, Carter	Sulaco	Consultant
Johnnie	Rico	2675-04-16	Roughneck31	[email protected]	2675-04-16	78440	LT Rasczak	Rodger Young	Private

I've already learned to do the dynamic column headers using

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

CodePudding user response：

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)