Pivot ในขณะที่การเปลี่ยนข้อมูลประเภท(dynamically)

Question 1

เอาเป็นว่าฉันมี 3 ตาราง:ใช้ customattributes และ customattributevalues. จุดจบของผู้ใช้สามารถเพิ่มกำหนดเองแอททริบิวต์ต่างๆโดยการเลือกชื่อและประเภทสำหรับแอททริบิวต์และแก้ไขค่าสำหรับพของผู้ใช้.

นี่คือของผู้ใช้:

หมายเลข	firstname	lastname	ที่ทำงานอยู่	datecreated	ชื่อผู้ใช้	อีเมล
3	เอลเลน	ริปลีย์	1	3/25/2235	78439	[email protected]
5	จอห์นนี่	Rico	1	4/16/2675	Roughneck31	[email protected]

customattributes(สามารถถูกเพิ่มเข้ากับเมื่อไรก็)

หมายเลข	fullname	uniquename	ประเภท
1	จ้างวัน	hiredate	เดท
2	ลูกจ้างของหมายเลข	eeid	int ได้
3	หัวหน้า	หัวหน้า	nvarchar(50)
4	กำหนดเรือ	assignedship	nvarchar(50)
5	หัวเรื่องงาน	jobtitle	nvarchar(50)

ประเภทฉันกำลังต้องเป็น sysname datatype.

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 datatype

ดังนั้นนี่คือของฉันคำถามยังไงฉันสามารถสร้างรายงานแสดงถึงเป็นพนักงานทั้งหมดและพวกของแอททริบิวต์ต่างๆ,1 บนเส้นต่อพนักงานโดยที่ไม่รู้ว่ากี่คนกำหนดเองคุณลักษณะยังมี--และ-- crucially ฉันอยากในข้อบังคับของแปลงแต่ละคอลัมน์ที่ถูกต้องข้อมูลประเภท

ที่ต้องการแสดงผล:

firstname	lastname	datecreated	ชื่อผู้ใช้	อีเมล	จ้างวัน	ลูกจ้างของหมายเลข	หัวหน้า	กำหนดเรือ	หัวเรื่องงาน
เอลเลน	ริปลีย์	2235-03-25	78439	[email protected]	2335-03-25	78439	เบิร์กคาร์เตอร์	Sulaco	ที่ปรึกษา
Johnnie	Rico	2675-04-16	Roughneck31	[email protected]	2675-04-16	78440	มัน Rasczak	Rodger ย	ส่วนตัว

ฉันบอกไปแล้วเรียนรู้ที่จะทำในแมสซีฟไดนามิคอลัมน์โดยใช้ส่วนหัว คลื่นบค้นข้อมูลแต่มันเป็นคนประเภทการแปลงนั่นคือหนีฉัน

ฉันกำวปรับเปลี่ยนรูปแบบ นี้ทางออกสำหรับช่องข้อมูลกำหนดเอง,แต่ limitation จะแก้ปัญหาก็คือคุณต้องรู้จักกำหนดเองสนามเพื่อทำให้คนประเภทการแปลง.

นี่คือสิ่งที่ฉันพยายามแล้ว ฉันต้องถูกส่งออกยกเว้นคนประเภท conversions.

กับการค้นหา:

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

Question 2

ถ้าคุณต้องเปลี่ยน datatype(น่าจะเป็นการนำเสนอเลเยอร์เรื่อง)นั้นปรับอัตโนมัติเงื่อนไขเซลบทั้งเธรดควรจะทำอย่างมหัศจรรย์จริงๆ

ตัวอย่างเช่น

Declare @SQL nvarchar(max) ='
Select U.*' +
(
Select concat(',',quotename(fullname),'=max(case when attributeid=',id,' then try_convert(',type,',value) end)')
 From customattributes
 For XML Path ('')
)+'
 From  users U
 Join  customattributesvalues V on U.ID=V.userid
 Group By U.ID
         ,U.FirstName
         ,U.LastName
         ,U.active
         ,U.datecreated
         ,U.username
         ,U.email
'
--print @SQL
Exec(@SQL)

ผลตรวจ

คนที่ถูกสร้างภาษา sql ดูเหมือนนี้

Select U.*
      ,[Hire Date]=max(case when attributeid=1 then try_convert(date,value) end)
      ,[Employee ID]=max(case when attributeid=2 then try_convert(int,value) end)
      ,[Supervisor]=max(case when attributeid=3 then try_convert(nvarchar(50),value) end)
      ,[Assigned Ship]=max(case when attributeid=4 then try_convert(nvarchar(50),value) end)
      ,[Job Title]=max(case when attributeid=5 then try_convert(nvarchar(50),value) end)
 From  #users U
 Join  #customattributesvalues V on U.ID=V.userid
 Group By U.ID
         ,U.FirstName
         ,U.LastName
         ,U.active
         ,U.datecreated
         ,U.username
         ,U.email

Question 3

SQL_VARIANT จะได้แสดงเป็นปลายทางข้อมูลประเภทนี้ได้นะ

แก้ไขส่วนหนึ่งของปรับอัตโนมัติกับการค้นหาที่คุณสร้างคอลัมน์รายการเพื่อสร้างสองรายการ หนึ่งรายการสำหรับ PIVOT ส่วนหนึ่งและอีกคนสำหรับ SELECT ส่วนหนึ่งที่คุณแสดงคุณข้อมูลประเภท.

ตัวอย่างเป็นเรื่องเกี่ยวกับพวกที่ บทความคุณคุณกล่าวถึงที่อยู่ในคำถามของคุณ:

DECLARE @PivotList NVARCHAR( MAX )
DECLARE @SelectList NVARCHAR( MAX )
SELECT @SelectList = NULL, @PivotList = NULL
        -- Column list with CAST e.g. CAST( eeid AS INT ) AS eeid
        -- Data types come from your customattributes table
SELECT @SelectList = COALESCE( @SelectList + ',','') + 'CAST( ' + uniquename + ' AS [type] ) AS ' + uniquename,
        -- Just a column list that goes into PIVOT operator
        @PivotList = COALESCE( @PivotList + ',','') + uniquename
-- Your tables for attribute values and attribute type definitions
FROM customattributes AS ca

DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery =

'SELECT StudID , '+@SelectList+'
FROM
( SELECT SM.StudID, S.SubjectName, SM.Score 
FROM StudentMarks SM 
INNER JOIN Subjects S
ON Sm.SubjectID = S.SubjectID
) AS tbl
PIVOT 
( Sum(Score)
FOR SubjectName IN ('+@PivotList+') ) as pvt'

EXEC(@SQLQuery)

John Cappelletti · Answer 1 · 2021-11-24T05:15:54

ถ้าคุณต้องเปลี่ยน datatype(น่าจะเป็นการนำเสนอเลเยอร์เรื่อง)นั้นปรับอัตโนมัติเงื่อนไขเซลบทั้งเธรดควรจะทำอย่างมหัศจรรย์จริงๆ

ตัวอย่างเช่น

Declare @SQL nvarchar(max) ='
Select U.*' +
(
Select concat(',',quotename(fullname),'=max(case when attributeid=',id,' then try_convert(',type,',value) end)')
 From customattributes
 For XML Path ('')
)+'
 From  users U
 Join  customattributesvalues V on U.ID=V.userid
 Group By U.ID
         ,U.FirstName
         ,U.LastName
         ,U.active
         ,U.datecreated
         ,U.username
         ,U.email
'
--print @SQL
Exec(@SQL)

ผลตรวจ

คนที่ถูกสร้างภาษา sql ดูเหมือนนี้

Select U.*
      ,[Hire Date]=max(case when attributeid=1 then try_convert(date,value) end)
      ,[Employee ID]=max(case when attributeid=2 then try_convert(int,value) end)
      ,[Supervisor]=max(case when attributeid=3 then try_convert(nvarchar(50),value) end)
      ,[Assigned Ship]=max(case when attributeid=4 then try_convert(nvarchar(50),value) end)
      ,[Job Title]=max(case when attributeid=5 then try_convert(nvarchar(50),value) end)
 From  #users U
 Join  #customattributesvalues V on U.ID=V.userid
 Group By U.ID
         ,U.FirstName
         ,U.LastName
         ,U.active
         ,U.datecreated
         ,U.username
         ,U.email

เจ๋งสุดยอด! มันมีทางที่จะแก้ไขเรื่องนี้ถึงกัดชนิดสามารถถูกใช้ได้? ฉันคิดว่าฉันสามารถออกไปกับไม่ไว้ใจได้บ้ากามแถมสัประดประเภทแต่ต้องขยายใหญ่สุดความยืดหยุ่ถ้าเป็นไปได้
@TristenHannah ขึ้นอยู่กับว่า ยังไง hare งชิ้นถูกเก็บไว้ในค่าโต๊ะ? 1/0 หรือเรื่องจริง/เท็จ
ฉันไม่ค่อยเหมือนกับเรื่องจริง/เท็จ opton-ฉันคิดว่าพวกเขาอยู่เสมอ 1/0
@TristenHannah คุณถูกเกี่ยวกับค 1/0. ฉันยังไม่ชัดเจนว่าคุณถูกจัดเก็บพวกเขา คุณกำลังบอก try_convert(หน่อยนะค่า)ไม่ทำงาน?
มันคือแม็กโอเปอเรเตอร์มันไม่ได้ผลและของฉันเข้าใจทั้งหมด aggregate operators ไม่ได้ผลหรอก อย่างไรก็ตามไม่มีเหตุผลที่จะสิ้นหวัง,ฉันคิดว่าฉันสามารถออกแบบอยู่ไม่อนุญาตให้หน่อประเภทข้อมูล.
นี่แก้ปัญหาเหมือนจะทำงานกับหน่วยข้อมูลประเภทแค่รุงภาพลักษณ์ที่สมบูรณ์ที่เลือกปรับอัตโนมัติไปยัง:เลือก concat(',',quotename(fullname),'=try_convert(',ประเภท'แม็กซ์(คดีตอนที่ attributeid=',ชื่อ'จากนั้นค่าสิ้นสุด))')(สี่เส้นในทางออก)

Alex · Answer 2 · 2021-11-24T04:41:32

SQL_VARIANT จะได้แสดงเป็นปลายทางข้อมูลประเภทนี้ได้นะ

แก้ไขส่วนหนึ่งของปรับอัตโนมัติกับการค้นหาที่คุณสร้างคอลัมน์รายการเพื่อสร้างสองรายการ หนึ่งรายการสำหรับ PIVOT ส่วนหนึ่งและอีกคนสำหรับ SELECT ส่วนหนึ่งที่คุณแสดงคุณข้อมูลประเภท.

ตัวอย่างเป็นเรื่องเกี่ยวกับพวกที่ บทความคุณคุณกล่าวถึงที่อยู่ในคำถามของคุณ:

DECLARE @PivotList NVARCHAR( MAX )
DECLARE @SelectList NVARCHAR( MAX )
SELECT @SelectList = NULL, @PivotList = NULL
        -- Column list with CAST e.g. CAST( eeid AS INT ) AS eeid
        -- Data types come from your customattributes table
SELECT @SelectList = COALESCE( @SelectList + ',','') + 'CAST( ' + uniquename + ' AS [type] ) AS ' + uniquename,
        -- Just a column list that goes into PIVOT operator
        @PivotList = COALESCE( @PivotList + ',','') + uniquename
-- Your tables for attribute values and attribute type definitions
FROM customattributes AS ca

DECLARE @SQLQuery NVARCHAR(MAX)
SET @SQLQuery =

'SELECT StudID , '+@SelectList+'
FROM
( SELECT SM.StudID, S.SubjectName, SM.Score 
FROM StudentMarks SM 
INNER JOIN Subjects S
ON Sm.SubjectID = S.SubjectID
) AS tbl
PIVOT 
( Sum(Score)
FOR SubjectName IN ('+@PivotList+') ) as pvt'

EXEC(@SQLQuery)

สวัสดีค่ะฉันตาม SelectList และ PivotList แต่ร้องขอแบบ sql ตัวมันเองดูเหมือนจะเป็นการอ้างอิงตารางข้อมูลเรียบร้อยแล้วกันไม่ได้ด้วยมันมีจำนวนไหม? ยังไงก็ฉันเห็นไหมว่ามันจะลงและฉันคิดว่าฉันสามารถใช้สงครามแย่งชิงยุทธศาสตอนเป็นไปได้ทางออก!

Pivot ในขณะที่การเปลี่ยนข้อมูลประเภท(dynamically)

คำถาม

คำตอบที่ดีที่สุด

ในภาษาอื่นๆ

หน้านี้อยู่ในภาษาอื่นๆ

ดังอยู่ในนี้หมวดหมู่

ดังคำถามอยู่ในนี้หมวดหมู่