Pivot în timp ce schimbarea tipuri de date (dinamic)

Question 1

Sa zicem ca am 3 tabele: utilizatori, customattributes, și customattributevalues. Utilizatorul final poate adăuga atribute personalizate prin selectarea unui nume și tip de atribut, și edita valorile pentru orice tip de utilizator.

Aici sunt meu utilizatori:

id	firstname	lastname	active	datecreated	numele de utilizator	e-mail
3	Ellen	Ripley	1	3/25/2235	78439	[email protected]
5	Johnny	Rico	1	4/16/2675	Roughneck31	[email protected]

customattributes (poate fi adăugat la orice moment)

id	fullname	uniquename	tip
1	Închiriere Data	hiredate	data
2	ID-ul angajatului	eeid	int
3	Supervizor	supervizor	nvarchar(50)
4	Atribuit Navei	assignedship	nvarchar(50)
5	Titlul De Locuri De Muncă	jobtitle	nvarchar(50)

de tip I au în prezent ca sysname tip de date.

customattributevalues (pot fi editate oricând)

id	attributeid	userid	valoarea
1	1	3	2335-03-25
2	2	3	78439
3	3	3	Burke, Carter
4	4	3	Sulaco
5	5	3	Consultant
6	1	5	2675-04-16
7	2	5	78440
8	3	5	LT Rasczak
9	4	5	Rodger Young
10	5	5	Privat

valoare au, în prezent, ca sql_variant tip de date

Deci, aici este intrebarea mea: cum pot crea un raport care afișează toți angajații și atributele lor, 1 linie pentru fiecare angajat, fără să știe cât de multe atribute personalizate sunt ... și ... foarte important, vreau să în mod explicit converti fiecare coloana de la tipul de date corect

Rezultatul dorit:

firstname	lastname	datecreated	numele de utilizator	e-mail	Închiriere Data	ID-ul angajatului	Supervizor	Atribuit Navei	Titlul De Locuri De Muncă
Ellen	Ripley	2235-03-25	78439	[email protected]	2335-03-25	78439	Burke, Carter	Sulaco	Consultant
Johnnie	Rico	2675-04-16	Roughneck31	[email protected]	2675-04-16	78440	LT Rasczak	Rodger Young	Privat

Am învățat deja să facă dinamic anteturile de coloană folosind interogări dinamice, dar acesta este tipul de conversie, care este de a scăpa de mine.

Am adaptarea această soluție pentru câmpuri personalizate, dar limitarea la această soluție este că trebuie să știu fiecare câmp personalizat pentru a face conversie de tip.

Aici este ceea ce am încercat. Am corectă de ieșire, cu excepția pentru conversii de tip.

Interogare:

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)

Crea Tabele:

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

Dacă trebuie să convertiți tip de date (ar putea fi într-adevăr o prezentare strat lucru), atunci o dinamică condiționată de agregare ar trebui să facă truc.

Exemplu

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)

Rezultate

Generate SQL Pare Ca Acest lucru

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 poate fi aruncat la o destinație tip de date.

Modifica o parte din interogare dinamic în cazul în care aveți de a genera lista pe o coloană, pentru a genera două liste. O listă este pentru PIVOT parte și cealaltă pentru SELECT partea în care ai turnat tu tipuri de date.

Exemplu se bazează pe articolul la care faceți referire în întrebarea ta:

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

Dacă trebuie să convertiți tip de date (ar putea fi într-adevăr o prezentare strat lucru), atunci o dinamică condiționată de agregare ar trebui să facă truc.

Exemplu

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)

Rezultate

Generate SQL Pare Ca Acest lucru

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

Genial! Există o modalitate de a modifica această deci cam tipuri pot fi folosite? Cred că pot să scape cu nu permite pic tipuri, dar ar dori pentru a maximiza flexibilitatea dacă este posibil.
@TristenHannah Depinde. Cum iepurele biți fiind stocate în tabelul valorilor? 1/0 sau adevărat/fals
Eu sunt familiarizat cu adevărat/fals opton - am gândit au fost întotdeauna 1/0
@TristenHannah sunt corecte cu privire la 1/0. Nu am fost clar pe cât ai fost de a le stoca. Vrei să spui try_convert(bit,valoare) nu merge?
e MAX operator care nu funcționează, și înțelegerea mea este tot agregate operatorii nu va funcționa. Cu toate acestea, nici un motiv de disperare, cred că pot jurul valorii de proiectare nu permite pic tipuri de date.
Această soluție pare să funcționeze cu bit tipuri de date doar modificarea dynamic select pentru: Select concat(',',quotename(fullname),'=try_convert(',tip,, MAX(cazul când attributeid=',id-ul, ATUNCI valoarea END))') (a patra linie în soluție)

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

SQL_VARIANT poate fi aruncat la o destinație tip de date.

Modifica o parte din interogare dinamic în cazul în care aveți de a genera lista pe o coloană, pentru a genera două liste. O listă este pentru PIVOT parte și cealaltă pentru SELECT partea în care ai turnat tu tipuri de date.

Exemplu se bazează pe articolul la care faceți referire în întrebarea ta:

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)

Salut, am urmat SelectList și PivotList, dar Interogare SQL în sine pare să-și fi permis alte mese nu sunt incluse. Totuși, am vedea în cazul în care acest lucru se întâmplă și cred că pot folosi conceptul ca o posibilă soluție!

Pivot în timp ce schimbarea tipuri de date (dinamic)

Problema

Cel mai bun răspuns

În alte limbi

Această pagină este în alte limbi

Popular în această categorie

Întrebări frecvente în această categorie