SQL Server 2008 PIVOT and JOIN

Abdou303

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 !

WhoamI

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

enter image description here

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related