让我们说有3个表:用户,customattributes,并customattributevalues. 终端用户可以增加定义的特性通过选择一个姓名和类型的属性,和编辑值为任何用户。
这里是我的用户:
id | firstname | lastname | 活性 | datecreated | 用户名 | 电子邮件 |
---|---|---|---|---|---|---|
3 | Ellen | 里普利 | 1 | 3/25/2235 | 78439 | [email protected] |
5 | 约翰尼 | 多黎各 | 1 | 4/16/2675 | Roughneck31 | [email protected] |
customattributes(可以被添加到随时)
id | fullname | 唯一名称 | 类型 |
---|---|---|---|
1 | 雇用日期 | hiredate | 日期 |
2 | 员工证 | eeid | int |
3 | 主管 | 主管 | 限(50) |
4 | 分配船 | assignedship | 限(50) |
5 | 工作标题 | jobtitle | 限(50) |
类型I目前有作为sysname数据类型。
customattributevalues(可以随时随地进行编辑)
id | attributeid | userid | 值 |
---|---|---|---|
1 | 1 | 3 | 2335-03-25 |
2 | 2 | 3 | 78439 |
3 | 3 | 3 | Burke,卡特 |
4 | 4 | 3 | Sulaco |
5 | 5 | 3 | 顾问 |
6 | 1 | 5 | 2675-04-16 |
7 | 2 | 5 | 78440 |
8 | 3 | 5 | LT Rasczak |
9 | 4 | 5 | 年轻的罗杰 |
10 | 5 | 5 | 私 |
值,我目前有作为 sql_variant
数据类型
因此,这里是我的问题:如何创建一份报告,显示了所有的雇员和他们的属性,1条,每个雇员,不知道有多少自定属性存在--和-- 最重要的是,我要明确转换的每个列为正确的数据类型
期望输出:
firstname | lastname | datecreated | 用户名 | 电子邮件 | 雇用日期 | 员工证 | 主管 | 分配船 | 工作标题 |
---|---|---|---|---|---|---|---|---|---|
Ellen | 里普利 | 2235-03-25 | 78439 | [email protected] | 2335-03-25 | 78439 | Burke,卡特 | Sulaco | 顾问 |
尊尼获加 | 多黎各 | 2675-04-16 | Roughneck31 | [email protected] | 2675-04-16 | 78440 | LT Rasczak | 年轻的罗杰 | 私 |
我已经学到了要做的动态列标题使用 的动态查询,但它是类型的转换,这是逃避我.
我在适应 这一解决方案定义的领域,但该限于这个方案是,你必须知道每个定义的领域做出的类型转换。
这里是我已经试过了。 我得到了正确的输出,除了为的类型转换。
查询:
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)
创建表:
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