Řekněme, že máme 3 tabulky: uživatelé, customattributes, a customattributevalues. Koncový uživatel může přidat vlastní atributy výběrem název a typ pro atribut, a upravte hodnoty pro všechny uživatele.
Zde jsou mé uživatelům:
| id | jméno | příjmení | aktivní | datecreated | uživatelské jméno | |
|---|---|---|---|---|---|---|
| 3 | Ellen | Ripley | 1 | 3/25/2235 | 78439 | [email protected] |
| 5 | Johnny | Rico | 1 | 4/16/2675 | Roughneck31 | [email protected] |
customattributes (mohou být přidány do kdykoliv)
| id | fullname | uniquename | typ |
|---|---|---|---|
| 1 | Auta Datum | hiredate | datum |
| 2 | ID zaměstnance | eeid | int |
| 3 | Vedoucí | vedoucí | nvarchar(50) |
| 4 | Přidělené Lodi | assignedship | nvarchar(50) |
| 5 | Název Práce | jobtitle | nvarchar(50) |
typ v současné době mám jako sysname datový typ.
customattributevalues (lze upravovat kdykoliv)
| id | attributeid | userid | hodnota |
|---|---|---|---|
| 1 | 1 | 3 | 2335-03-25 |
| 2 | 2 | 3 | 78439 |
| 3 | 3 | 3 | Burke, Carter |
| 4 | 4 | 3 | Sulaco |
| 5 | 5 | 3 | Konzultant |
| 6 | 1 | 5 | 2675-04-16 |
| 7 | 2 | 5 | 78440 |
| 8 | 3 | 5 | PORUČÍK Rasczak |
| 9 | 4 | 5 | Roger Young |
| 10 | 5 | 5 | Soukromé |
hodnota v současné době mám jako sql_variant datový typ
Tak tady je moje otázka: jak mohu vytvořit sestavu, která zobrazuje všechny zaměstnance a jejich atributy, 1 řádek za zaměstnance, aniž by věděl, kolik vlastní atributy tam jsou ... a ... především chci explicitně převést každý sloupec na správný datový typ
Požadovaný výstup:
| jméno | příjmení | datecreated | uživatelské jméno | Auta Datum | ID zaměstnance | Vedoucí | Přidělené Lodi | Název Práce | |
|---|---|---|---|---|---|---|---|---|---|
| Ellen | Ripley | 2235-03-25 | 78439 | [email protected] | 2335-03-25 | 78439 | Burke, Carter | Sulaco | Konzultant |
| Johnnie | Rico | 2675-04-16 | Roughneck31 | [email protected] | 2675-04-16 | 78440 | PORUČÍK Rasczak | Roger Young | Soukromé |
Už jsem se naučil dělat dynamické záhlaví sloupců pomocí dynamické dotazy, ale je to typ konverze, která je útěk mě.
Mám přizpůsobení tohoto řešení pro vlastní pole, ale omezení tohoto řešení je, že musíte vědět, každé vlastní pole, aby se typ konverze.
Zde je to, co jsem se snažil. Mám správný výstup, s výjimkou pro typ konverze.
Dotaz:
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)
Vytvořit Tabulky:
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
