I have 3 tables with data:
TABLE 1 : Type
IdType LibelleType
-------------------
1 Type1
2 Type2
TABLE 2 : Procedure
IdProcedur LibelleProcedure
-------------------------------
1 Procedure1
2 Procedure2
TABLE 3 : INFORMATION
IdInfo IdType IdProcedure InfoValue
------------------------------------------------
1 1 1 11
2 1 2 12
3 2 1 21
4 2 2 22
I need a query which will produce this output:
Procedure1 Procedure2
Type1 11 12
Type2 21 22
Thank you !
Please check below. The last select does the pivot.(My earlier queries are just to prepare the dataset)
create table Type
(IDType INT,Label VARCHAR(100)
)
create table [PROC]
(IDProc INT, LabelProc varchar(100))
Create table INFO
(IDInfo INT, IDType INT, IDProc INT, INFOValue INT)
insert into Type
values(1,'Type1'),(2,'Type2')
insert into [PROC]
values(1,'Proc1'),(2,'Proc2')
insert into InFO
values(1,1,1,11),(2,1,2,12),(3,2,1,21),(4,2,2,22)
select * from Type
select * from [PROC]
select * from info
declare @labelforprocs varchar(max) = ''
,@sql NVARCHAR(MAX)
select @labelforprocs = CONCAT(@labelforprocs,QUOTENAME(LabelProc),',') from [PROC]
select @labelforprocs = LEFT(@labelforprocs,LEN(@labelforprocs)-1)
SET @sql =
'select * from
(
select T.Label,P.LabelProc,I.INFOValue from INFO I
INNER JOIN [PROC] P
ON I.IDPROC = P.IDProc
INNER JOIN TYPE T
on T.IDType = I.IDType
)SRC
PIVOT
(MAX(INFOValue)
FOR LabelProc in (' + @labelforprocs +
'))piv'
EXEC sp_executesql @sql
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments