चलो कहते हैं मैं 3 टेबल: उपयोगकर्ताओं ग ustom एक, और customattributevalues. अंत उपयोगकर्ता जोड़ सकते हैं कस्टम विशेषताओं का चयन करके एक नाम और प्रकार के लिए विशेषता है, और मान संपादित करें किसी भी उपयोगकर्ता के लिए.
यहाँ मेरे उपयोगकर्ता:
| आईडी | firstname | अंतिम नाम | सक्रिय | datecreated | उपयोगकर्ता नाम | ईमेल |
|---|---|---|---|---|---|---|
| 3 | एलेन | Ripley | 1 | 3/25/2235 | 78439 | [email protected] |
| 5 | जॉनी | रिको | 1 | 4/16/2675 | Roughneck31 | [email protected] |
ग ustom एक (कर सकते हैं जोड़ा जा करने के लिए किसी भी समय)
| आईडी | fullname | uniquename | प्रकार |
|---|---|---|---|
| 1 | किराए की तारीख | hiredate | तारीख |
| 2 | कर्मचारी आईडी | eeid | int |
| 3 | पर्यवेक्षक | पर्यवेक्षक | nvarchar(50) |
| 4 | सौंपा जहाज | assignedship | nvarchar(50) |
| 5 | नौकरी शीर्षक | jobtitle | nvarchar(50) |
प्रकार मैं वर्तमान में है के रूप में sysname डेटा प्रकार.
customattributevalues (संपादित किया जा सकता है किसी भी समय)
| आईडी | attributeid | userid | मूल्य |
|---|---|---|---|
| 1 | 1 | 3 | 2335-03-25 |
| 2 | 2 | 3 | 78439 |
| 3 | 3 | 3 | बर्क, कार्टर |
| 4 | 4 | 3 | Sulaco |
| 5 | 5 | 3 | सलाहकार |
| 6 | 1 | 5 | 2675-04-16 |
| 7 | 2 | 5 | 78440 |
| 8 | 3 | 5 | लेफ्टिनेंट Rasczak |
| 9 | 4 | 5 | Rodger युवा |
| 10 | 5 | 5 | निजी |
मूल्य मैं वर्तमान में है के रूप में sql_variant डेटा प्रकार
तो यहाँ मेरा सवाल है: मैं कैसे कर सकते हैं बनाने के लिए एक रिपोर्ट से पता चलता है कि सभी कर्मचारियों और उनके गुण, 1 लाइन प्रति कर्मचारी कैसे जानने के बिना, कई कस्टम विशेषताओं, वहाँ रहे हैं-और - महत्वपूर्ण बात, मैं करने के लिए चाहते हैं स्पष्ट रूप से परिवर्तित करने के लिए प्रत्येक स्तंभ सही डेटा प्रकार
वांछित आउटपुट:
| firstname | अंतिम नाम | datecreated | उपयोगकर्ता नाम | ईमेल | किराए की तारीख | कर्मचारी आईडी | पर्यवेक्षक | सौंपा जहाज | नौकरी शीर्षक |
|---|---|---|---|---|---|---|---|---|---|
| एलेन | Ripley | 2235-03-25 | 78439 | [email protected] | 2335-03-25 | 78439 | बर्क, कार्टर | Sulaco | सलाहकार |
| Johnnie | रिको | 2675-04-16 | Roughneck31 | [email protected] | 2675-04-16 | 78440 | लेफ्टिनेंट Rasczak | Rodger युवा | निजी |
मैं पहले से ही सीखा है क्या करने के लिए गतिशील कॉलम हेडर का उपयोग कर गतिशील क्वेरी, लेकिन यह प्रकार है कि रूपांतरण से बचने के है ।
मैं हूँ आदत डाल इस समाधान के लिए कस्टम क्षेत्रों, लेकिन सीमा के लिए इस समाधान है, आप प्रत्येक पता करने के लिए कस्टम फ़ील्ड बनाने के लिए प्रकार रूपांतरण ।
यहाँ है क्या मैंने कोशिश की है. मैं सही उत्पादन के लिए छोड़कर, प्रकार रूपांतरण.
क्वेरी:
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
