말하자 나는 3 테이블:사용자 customattributes 및 customattributevalues. 최종 사용자 사용할 수 있는 사용자 정의 속성을 추가하려면 선택하여 이름과 형식에 대한 특성과 편집에 대한 값이 사용자가 없습니다.
여기 내용:
id | 이름 | lastname | 활성 | datecreated | 사용자 이름 | 이메일 |
---|---|---|---|---|---|---|
3 | Ellen | 리 | 1 | 3/25/2235 | 78439 | [email protected] |
5 | Johnny | 리코 | 1 | 4/16/2675 | Roughneck31 | [email protected] |
customattributes(추가될 수 있는 언제든지)
id | fullname | 재검 | 유형 |
---|---|---|---|
1 | 량 날짜 | hiredate | 날짜 |
2 | 직원 ID | eeid | int |
3 | 관리자 | 관리자 | 이며(50) |
4 | 에 할당된 선박 | assignedship | 이며(50) |
5 | 직위 | 직함 | 이며(50) |
종 현재로 이며 데이터 형식.
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 | Private |
값을 나는 현재로 sql_variant
데이터 형식
그래서 여기에 제 질문:수 있는 방법을 보여주는 보고서를 만들은 모든 직원 및 그들의 특성,1 행당 직원은 모르고,얼마나 많은 사용자 지정 특성이 있다 그리고 그 결정적으로,내가 원하는 명시적으로 각각 변하는 열이 올바른 데이터의 유형
원하는 출력:
이름 | lastname | datecreated | 사용자 이름 | 이메일 | 량 날짜 | 직원 ID | 관리자 | 에 할당된 선박 | 직위 |
---|---|---|---|---|---|---|---|---|---|
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 | 로저 젊 | Private |
나는 이미 배운 것을 동적 열 헤더를 사용하여 동적 쿼리,그러나 그것은 유형을 변환하는 것은 탈출하는 나입니다.
나는 적응하 는 이 솔루션을 위한 사용자 정의 필드는,하지만 제한하는 이 솔루션을 알고 있어야 각 사용자 정의 필드를 입력 변환입니다.
여기에 내가 무엇을 시도했다. 내가 가지고 올바른 출력을 제외한 형식 변환.
쿼리:
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