Povedzme, že mám 3 tabuľky: používatelia, customattributes, a customattributevalues. Koncový užívateľ môžete pridať vlastné atribúty výberom názov a typ atribútu, a upravte hodnoty pre ľubovoľného používateľa.
Tu sú moje používateľov:
| id | meno | priezvisko | aktívne | text datecreated | užívateľské meno | |
|---|---|---|---|---|---|---|
| 3 | Ellen | Ripley | 1 | 3/25/2235 | 78439 | [email protected] |
| 5 | Johnny | Rico | 1 | 4/16/2675 | Roughneck31 | [email protected] |
customattributes (môžu byť pridané hocikedy)
| id | fullname | uniquename | druh |
|---|---|---|---|
| 1 | Prenájom Dátum | hiredate | dátum |
| 2 | Zamestnanec ID | eeid | int |
| 3 | Garant | garant | nvarchar(50) |
| 4 | Pridelené Lode | assignedship | nvarchar(50) |
| 5 | Názov Práce | jobtitle | nvarchar(50) |
typ I v súčasnosti, ako sysname typ.
customattributevalues (možno upravovať kedykoľvek)
| id | attributeid | userid | hodnota |
|---|---|---|---|
| 1 | 1 | 3 | 2335-03-25 |
| 2 | 2 | 3 | 78439 |
| 3 | 3 | 3 | Burke, Carter |
| 4 | 4 | 3 | Na perzskom koberci |
| 5 | 5 | 3 | Konzultant |
| 6 | 1 | 5 | 2675-04-16 |
| 7 | 2 | 5 | 78440 |
| 8 | 3 | 5 | LT Rasczak |
| 9 | 4 | 5 | Rodger Mladých |
| 10 | 5 | 5 | Súkromné |
hodnoty v súčasnej dobe sa ako sql_variant typ
Takže tu je moja otázka: ako môžem vytvoriť správu, ktorá ukazuje, všetkých zamestnancov a ich atribúty, 1 riadok za zamestnanca, bez toho aby vedel, koľko vlastné atribúty, ktoré existujú ---- a čo je najdôležitejšie, chcem explicitne konvertovať každý stĺpec na správny typ údajov
Požadovaný výstup:
| meno | priezvisko | text datecreated | užívateľské meno | Prenájom Dátum | Zamestnanec ID | Garant | Pridelené Lode | Názov Práce | |
|---|---|---|---|---|---|---|---|---|---|
| Ellen | Ripley | 2235-03-25 | 78439 | [email protected] | 2335-03-25 | 78439 | Burke, Carter | Na perzskom koberci | Konzultant |
| Johnnie | Rico | 2675-04-16 | Roughneck31 | [email protected] | 2675-04-16 | 78440 | LT Rasczak | Rodger Mladých | Súkromné |
Už som sa naučil robiť dynamické hlavičky stĺpcov pomocou dynamického dotazy, ale to je typ konverzie, že je na úteku mňa.
Som prispôsobenie toto riešenie pre vlastné polia, ale obmedzenie tohto riešenia je, že máte vedieť každý vlastné pole, aby typu konverzie.
Tu je to, čo som sa snažil. Dostal som správne výstup, okrem typu konverzie.
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)
Vytvoriť Tabuľky:
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
