Pivote, mientras que el cambio de tipos de datos (de forma dinámica)

Question 1

Digamos que tengo 3 tablas: usuarios, customattributes, y customattributevalues. El usuario final puede añadir atributos personalizados mediante la selección de un nombre y el tipo del atributo, y la edición de los valores de cualquier usuario.

Aquí están mis usuarios:

id	nombre	apellido	activo	datecreated	nombre de usuario	correo electrónico
3	Elena	Ripley	1	3/25/2235	78439	[email protected]
5	Johnny	Rico	1	4/16/2675	Roughneck31	[email protected]

customattributes (puede ser añadido en cualquier momento)

id	fullname	uniquename	tipo de
1	Fecha De Contratación	hiredate	fecha
2	ID de empleado	eeid	int
3	Supervisor	supervisor	nvarchar(50)
4	Asignado Barco	assignedship	nvarchar(50)
5	Título Del Trabajo	jobtitle	nvarchar(50)

el tipo que tengo actualmente como de tipo sysname y su tipo de datos.

customattributevalues (puede ser editado en cualquier momento)

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

el valor que tengo actualmente como sql_variant tipo de datos

Así que aquí está mi pregunta: ¿cómo puedo crear un informe que muestra todos los empleados y sus atributos, 1 línea por empleado, sin saber cómo muchos de los atributos personalizados hay --y-- fundamentalmente, quiero convertir explícitamente cada columna para el tipo de datos correcto

Salida deseada:

nombre	apellido	datecreated	nombre de usuario	correo electrónico	Fecha De Contratación	ID de empleado	Supervisor	Asignado Barco	Título Del Trabajo
Elena	Ripley	2235-03-25	78439	[email protected]	2335-03-25	78439	Burke, Carter	Sulaco	Consultor
Johnnie	Rico	2675-04-16	Roughneck31	[email protected]	2675-04-16	78440	LT Rasczak	Rodger Young	Privado

Ya he aprendido a hacer la dinámica de los encabezados de columna mediante consultas dinámicas, pero es el tipo de conversión que se escapa de mí.

Me estoy adaptando a esta solución para los campos personalizados, pero la limitación de esta solución es que usted tiene que saber cada campo personalizado para hacer la conversión de tipo.

Aquí es lo que he intentado. Llegué a la salida correcta, excepto para las conversiones de tipo.

Consulta:

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)

Crear Tablas:

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 IF NOT EXISTS [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 IF NOT EXISTS [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 IF NOT EXISTS [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

Question 2

Si usted debe convertir el tipo de datos (que realmente podría ser una capa de presentación cosa), entonces una dinámica condicional de agregación debe hacer el truco.

Ejemplo

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)

Resultados

El SQL Generado se Parece a Esto

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

Question 3

SQL_VARIANT se puede convertir un tipo de datos de destino.

Modificar parte de la dinámica de la consulta que genera la lista de columnas, para generar dos listas. Una lista es para PIVOT parte y la otra para SELECT la parte donde se echó tipos de datos.

El ejemplo se basa en el artículo que se refiere en su pregunta:

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)

John Cappelletti · Answer 1 · 2021-11-24T05:15:54

Si usted debe convertir el tipo de datos (que realmente podría ser una capa de presentación cosa), entonces una dinámica condicional de agregación debe hacer el truco.

Ejemplo

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)

Resultados

El SQL Generado se Parece a Esto

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

Genial! Hay una forma de modificar esto para que poco pueden utilizarse los tipos? Creo que me puede conseguir lejos con no permitir bits tipos, pero quisiera para maximizar la flexibilidad, si es posible.
@TristenHannah Depende. Cómo hare los bits se almacenan en la tabla de valores? 1/0 o de verdadero/falso
Estoy familiarizado con verdadero/falso opton - pensé que eran siempre 1/0
@TristenHannah estás en lo correcto con respecto a la 1/0. No estaba claro cómo es que se fueron de almacenarlos. Estás diciendo try_convert(bit,valor) no funciona?
es el máximo operador que no funciona, y mi entendimiento es que todos los operadores de agregado no funciona. Sin embargo, no hay razón para la desesperación, creo que puedo diseño en torno a no permitir que los tipos de datos bit.
Esta solución parece trabajar con tipos de datos bit sólo la modificación de la dinámica de seleccionar a: Select concat(',',quotename(fullname),'=try_convert(',tipo,',MAX(caso cuando attributeid='id', EL valor FINAL))') (cuarta línea en la solución)

Alex · Answer 2 · 2021-11-24T04:41:32

SQL_VARIANT se puede convertir un tipo de datos de destino.

Modificar parte de la dinámica de la consulta que genera la lista de columnas, para generar dos listas. Una lista es para PIVOT parte y la otra para SELECT la parte donde se echó tipos de datos.

El ejemplo se basa en el artículo que se refiere en su pregunta:

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)

Hola, yo soy de la SelectList y PivotList, pero el SQL de la Consulta en sí mismo parece ser la referencia a otras tablas no incluidas. Aún puedo ver a dónde va esto, y creo que se puede utilizar el concepto como una posible solución!

Pivote, mientras que el cambio de tipos de datos (de forma dinámica)

Pregunta

Mejor respuesta

En otros idiomas

Esta página está en otros idiomas

Popular en esta categoría

Las preguntas más habituales en esta categoría