Veri türlerini değiştirirken Pivot (dinamik olarak)

Question 1

Diyelim ki 3 tablom var: kullanıcılar, customattributes ve customattributevalues. Son kullanıcı, öznitelik için bir ad ve tür seçerek ve herhangi bir kullanıcının değerlerini düzenleyerek özel öznitelikler ekleyebilir.

İşte kullanıcılarım:

kimlik	ilk isim	Soyadı	etkin	tarih oluşturuldu	Kullanıcı adı	e-posta
3	Ellen	Ripley	1	3/25/2235	78439	[email protected]
5	Herif	Rıco	1	4/16/2675	Kaba Boy31	[email protected]

customattributes (istediğiniz zaman eklenebilir)

kimlik	tam adı	eşsizadı	tip
1	İşe Alım Tarihi	işe alma	tarih
2	Çalışan Kimliği	eeıd	tamsayı
3	Yönetici	yönetici	nvarchar(50)
4	Atanmış Gemi	atanmış gemi	nvarchar(50)
5	unvan	iş başlığı	nvarchar(50)

şu anda sysname veri türü olarak sahip olduğum tür.

customattributevalues (istediğiniz zaman düzenlenebilir)

kimlik	attributeıdname	kullanıcı kimliği	değer
1	1	3	2335-03-25
2	2	3	78439
3	3	3	Burke, Carter.
4	4	3	Sulaco
5	5	3	Danışman
6	1	5	2675-04-16
7	2	5	78440
8	3	5	TEĞMEN Rasczak
9	4	5	Rodger Genç
10	5	5	Özel

şu anda sahip olduğum değer sql_variant veri türü

İşte sorum şu :Kaç tane özel öznitelik olduğunu bilmeden, çalışan başına 1 satır olan tüm çalışanları ve özniteliklerini gösteren bir raporu nasıl oluşturabilirim-ve en önemlisi, her sütunu açıkça doğru veri türüne dönüştürmek istiyorum

İstenen çıktı:

ilk isim	Soyadı	tarih oluşturuldu	Kullanıcı adı	e-posta	İşe Alım Tarihi	Çalışan Kimliği	Yönetici	Atanmış Gemi	unvan
Ellen	Ripley	2235-03-25	78439	[email protected]	2335-03-25	78439	Burke, Carter.	Sulaco	Danışman
Johnnie	Rıco	2675-04-16	Kaba Boy31	[email protected]	2675-04-16	78440	TEĞMEN Rasczak	Rodger Genç	Özel

Dinamik sorguları kullanarak dinamik sütun başlıklarını yapmayı zaten öğrendim, ancak benden kaçan tür dönüşümü.

Bu çözümü özel alanlar için uyarlıyorum, ancak bu çözümün sınırlaması, tür dönüşümünü yapmak için her özel alanı bilmeniz gerektiğidir.

İşte denediklerim. Tür dönüşümleri dışında doğru çıktıyı aldım.

Sorgu:

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)

Tablo Oluşturma:

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

Veri türünü dönüştürmeniz gerekiyorsa (gerçekten bir sunum katmanı olabilir), dinamik bir koşullu toplama hile yapmalıdır.

Örnek

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)

Sonuçlar

Oluşturulan SQL Şöyle Görünür

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 hedef veri türüne aktarılabilir.

İki liste oluşturmak için sütun listesi oluşturduğunuz dinamik sorgunun bir bölümünü değiştirin. Bir liste için PIVOT bir kısmı ve diğeri için SELECT veri türlerinizi attığınız bölüm.

Örnek, sorunuzda atıfta bulunduğunuz makaleye dayanmaktadır:

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

Veri türünü dönüştürmeniz gerekiyorsa (gerçekten bir sunum katmanı olabilir), dinamik bir koşullu toplama hile yapmalıdır.

Örnek

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)

Sonuçlar

Oluşturulan SQL Şöyle Görünür

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

Harika! Bunu değiştirmenin bir yolu var mı, böylece bit türleri kullanılabilir mi? Bit türlerine izin vermemekten kurtulabileceğimi düşünüyorum, ancak mümkünse esnekliği en üst düzeye çıkarmak istiyorum.
@TristenHannah Bağlıdır. Değerler tablosunda saklanan bitler nasıl saklanır? 1/0 veya doğru / yanlış
Doğru / yanlış opton'a aşina değilim-her zaman 1/0 olduklarını düşündüm
@TristenHannah 1/0 konusunda haklısın. Onları nasıl sakladığın konusunda net değildim. Try_convert(bit,değer) çalışmıyor mu diyorsunuz?
bu MAX operatörü iş değil, ve benim anlayış tüm toplama operatörleri işe yaramaz. Ancak, umutsuzluğa kapılmak için bir neden yok, bit veri türlerine izin vermemeyi tasarlayabileceğimi düşünüyorum.
Bu çözüm, yalnızca dinamik seçimi değiştiren bit veri türleriyle çalışıyor gibi görünüyor: Select concat(',',quotename(fullname),'=try_convert(',type,',MAX(attributeıd=',id,' SONRA değer SONU))') (çözümdeki dördüncü satır)

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

SQL_VARIANT hedef veri türüne aktarılabilir.

İki liste oluşturmak için sütun listesi oluşturduğunuz dinamik sorgunun bir bölümünü değiştirin. Bir liste için PIVOT bir kısmı ve diğeri için SELECT veri türlerinizi attığınız bölüm.

Örnek, sorunuzda atıfta bulunduğunuz makaleye dayanmaktadır:

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)

Merhaba, SelectList ve Pivotlist'i takip ediyorum, ancak SQL Sorgusunun kendisi dahil olmayan diğer tablolara başvuruyor gibi görünüyor. Yine de, bunun nereye gittiğini görüyorum ve konsepti olası bir çözüm olarak kullanabileceğimi düşünüyorum!

Veri türlerini değiştirirken Pivot (dinamik olarak)

Soru

En iyi cevabı

Diğer dillerde

Bu sayfa diğer dillerde

Bu kategoride popüler

Popüler soruları bu kategoride