Generate data matrix with LEFT JOINS in SQL Server

Question

Generate data matrix with LEFT JOINS in SQL Server

Navigation

#1 by (1 votes)

0

Good afternoon ... I have this temporary table with the following structure:

create table ##PAC_ID_TEMP ( Id INT PRIMARY KEY IDENTITY(1,1),NHC varchar(30), Id_Ficha_IOS int) CREATE NONCLUSTERED  INDEX Idx1 ON ##PAC_ID_TEMP(NHC);

Which I use to filter the information I want to show, this table I use in the following script.

	
	
	




select  f.Id_Ficha_IOS,
	e1.Id_Enfermedad, e1.Id_FichaIOS, e1.Id_Medicamento, e1.Id_Tipo_Enfermedad, e1.Id_TipoMedicamento,
	e2.Id_Enfermedad, e2.Id_FichaIOS, e2.Id_Medicamento, e2.Id_Tipo_Enfermedad, e2.Id_TipoMedicamento,
	e3.Id_Enfermedad, e3.Id_FichaIOS, e3.Id_Medicamento, e3.Id_Tipo_Enfermedad, e3.Id_TipoMedicamento,
	e4.Id_Enfermedad, e4.Id_FichaIOS, e4.Id_Medicamento, e4.Id_Tipo_Enfermedad, e4.Id_TipoMedicamento,
	e5.Id_Enfermedad, e5.Id_FichaIOS, e5.Id_Medicamento, e5.Id_Tipo_Enfermedad, e5.Id_TipoMedicamento,
	e6.Id_Enfermedad, e6.Id_FichaIOS, e6.Id_Medicamento, e6.Id_Tipo_Enfermedad, e6.Id_TipoMedicamento
	from ##PAC_ID_TEMP f
	left join (
		select e.Id_FichaIOS ,  Id_Enfermedad , Id_Tipo_Enfermedad  , Id_TipoMedicamento , Id_Medicamento    
		from ##PAC_ID_TEMP f 
		left join Enfermedad_Paciente_FichaIOS e
		on f.Id_Ficha_IOS =  e.Id_FichaIOS
		left join Medicamento_Administrado m
		on e.Id_FichaIOS = m.Id_FichaIOS 
	)e1
	on f.Id_Ficha_IOS = e1.Id_FichaIOS
	left join 
	(
		select e.Id_FichaIOS ,  Id_Enfermedad , Id_Tipo_Enfermedad  , Id_TipoMedicamento , Id_Medicamento    
		from ##PAC_ID_TEMP f 
		left join Enfermedad_Paciente_FichaIOS e
		on f.Id_Ficha_IOS =  e.Id_FichaIOS
		left join Medicamento_Administrado m
		on e.Id_FichaIOS = m.Id_FichaIOS
	)e2
	on f.Id_Ficha_IOS = e2.Id_FichaIOS
	left join (
		select e.Id_FichaIOS ,  Id_Enfermedad , Id_Tipo_Enfermedad  , Id_TipoMedicamento , Id_Medicamento    
		from ##PAC_ID_TEMP f 
		left join Enfermedad_Paciente_FichaIOS e
		on f.Id_Ficha_IOS =  e.Id_FichaIOS
		left join Medicamento_Administrado m
		on e.Id_FichaIOS = m.Id_FichaIOS
	)e3
	on f.Id_Ficha_IOS = e3.Id_FichaIOS
	left join (
		select e.Id_FichaIOS ,  Id_Enfermedad , Id_Tipo_Enfermedad  , Id_TipoMedicamento , Id_Medicamento    
		from ##PAC_ID_TEMP f 
		left join Enfermedad_Paciente_FichaIOS e
		on f.Id_Ficha_IOS =  e.Id_FichaIOS
		left join Medicamento_Administrado m
		on e.Id_FichaIOS = m.Id_FichaIOS
	)e4
	on f.Id_Ficha_IOS = e4.Id_FichaIOS
	left join (
		select e.Id_FichaIOS ,  Id_Enfermedad , Id_Tipo_Enfermedad  , Id_TipoMedicamento , Id_Medicamento    
		from ##PAC_ID_TEMP f 
		left join Enfermedad_Paciente_FichaIOS e
		on f.Id_Ficha_IOS =  e.Id_FichaIOS
		left join Medicamento_Administrado m
		on e.Id_FichaIOS = m.Id_FichaIOS
	)e5
	on f.Id_Ficha_IOS = e5.Id_FichaIOS
		left join (
		select e.Id_FichaIOS ,  Id_Enfermedad , Id_Tipo_Enfermedad  , Id_TipoMedicamento , Id_Medicamento    
		from ##PAC_ID_TEMP f 
		left join Enfermedad_Paciente_FichaIOS e
		on f.Id_Ficha_IOS =  e.Id_FichaIOS
		left join Medicamento_Administrado m
		on e.Id_FichaIOS = m.Id_FichaIOS
	)e6
	on f.Id_Ficha_IOS = e6.Id_FichaIOS
	group by f.Id_Ficha_IOS,
	e1.Id_Enfermedad, e1.Id_FichaIOS, e1.Id_Medicamento, e1.Id_Tipo_Enfermedad, e1.Id_TipoMedicamento,
	e2.Id_Enfermedad, e2.Id_FichaIOS, e2.Id_Medicamento, e2.Id_Tipo_Enfermedad, e2.Id_TipoMedicamento,
	e3.Id_Enfermedad, e3.Id_FichaIOS, e3.Id_Medicamento, e3.Id_Tipo_Enfermedad, e3.Id_TipoMedicamento,
	e4.Id_Enfermedad, e4.Id_FichaIOS, e4.Id_Medicamento, e4.Id_Tipo_Enfermedad, e4.Id_TipoMedicamento,
	e5.Id_Enfermedad, e5.Id_FichaIOS, e5.Id_Medicamento, e5.Id_Tipo_Enfermedad, e5.Id_TipoMedicamento,
	e6.Id_Enfermedad, e6.Id_FichaIOS, e6.Id_Medicamento, e6.Id_Tipo_Enfermedad, e6.Id_TipoMedicamento

What I hope to get with this script is a matrix like the following:

This result is expected considering that the data I have registered is the following:

Id_FichaIOS Id_Paciente Fecha                   Fecha_Enfermedad        Id_Tipo_Enfermedad Id_Enfermedad
----------- ----------- ----------------------- ----------------------- ------------------ -------------
213         9607        2017-06-27 12:02:36.030 2017-05-24 00:00:00.000 3                  13521        
213         9607        2017-06-27 12:03:48.540 2017-05-24 00:00:00.000 3                  68           
213         9607        2017-06-27 12:04:52.200 2017-05-24 00:00:00.000 1                  26           
213         9607        2017-06-27 12:07:13.380 2017-05-24 00:00:00.000 2                  2            
213         9607        2017-06-27 12:09:24.630 2017-06-07 00:00:00.000 1                  22           
199         9560        2017-06-26 14:21:54.743 2017-05-11 00:00:00.000 2                  6            
199         9560        2017-06-26 14:24:43.243 2017-05-15 00:00:00.000 1                  20           
199         9560        2017-06-26 14:25:47.050 2017-05-31 00:00:00.000 2                  2

However I have two problems, the structure of joins takes too long and I do not get the expected result, I get something like this ...

can you give me some guidance please.

Thank you.

------------------- edited at this point ------------------------ ------------

The script that you contributed to this:

select 'max (case when  enf.Nro ='+ cast ( Conteo as varchar (5)) + ' ' +'then '+ ' ' + x.Campo+ ' ' +'else null end)' +x.Campo+ cast (Nro as varchar(2)) 
from
(
	select Conteo, #row, Campo, Nro,Id_FichaIOS, Id_Enfermedad, Fecha_Enfermedad
	from
	(
		select #vt.Conteo Conteo,   tt.Campo Campo,#row
		from ##ValoresPorTabla #vt 
		cross apply
		(
			select Campo , 
			ROW_NUMBER() over (  order by  Campo) #row
			from TablaCampoInformes
			where Tabla ='Enfermedad_Paciente_FichaIOS' and Campo <> 'Id_FichaIOS'
		)tt

		where #vt.Tabla ='Enfermedad_Paciente_FichaIOS'
		--order by Conteo, #row
	)n
	left join 
	(
		SELECT  Id_FichaIOS,
		Id_Enfermedad, Fecha_Enfermedad,
		ROW_NUMBER() OVER (PARTITION BY Id_FichaIOS ORDER BY Id_Enfermedad) AS Nro
		FROM Enfermedad_Paciente_FichaIOS --where Id_FichaIOS in (199,213)

	)enf
	on n.Conteo = enf.Nro
	--
)x
order by Id_FichaIOS, Nro, #row

This section of the script tells me how many columns I should generate:

select #vt.Conteo Conteo,   tt.Campo Campo,#row
    from ##ValoresPorTabla #vt 
    cross apply
    (
        select Campo , 
        ROW_NUMBER() over (  order by  Campo) #row
        from TablaCampoInformes
        where Tabla ='Enfermedad_Paciente_FichaIOS' and Campo <> 'Id_FichaIOS'
    )tt

My problem is that I can not generate the amount of case, indicated by this table

------------------------------ ESCRIPT TO SOLVE THE DYNAMIC AMOUNT OF CASE TO GENERATE ------- ------------------------

select 'max (case when enf.Nro = ' + cast(Conteo as varchar(4) )+ ' ' +'then enf.' + Campo + ' ' +'else null end) as ' +Campo+cast (Conteo as varchar(4))
from
(
	select #vt.Conteo Conteo,   tt.Campo Campo,#row
	from ##ValoresPorTabla #vt 
	cross apply
	(
		select Campo , 
		ROW_NUMBER() over (  order by  Campo) #row
		from TablaCampoInformes
		where Tabla ='Enfermedad_Paciente_FichaIOS' and Campo <> 'Id_FichaIOS'
	)tt

	where #vt.Tabla ='Enfermedad_Paciente_FichaIOS'
	--order by Conteo, #row
)x	
order by Conteo, #row

sql sql-server

asked by Emerson Rios 18.09.2017 в 22:07

source

1 answer

Can you connect an Android Xamarin App, to sql server directly or is it necessary to consume some WebApi or some other way? Autocomplete Finder AngularJS + SQL SERVER does not show results

score 1 · Accepted Answer

Your most important problem is not the filter of the data by ##PAC_ID_TEMP but the "transpocision" of rows to columns. Doing this always brings with it some other headache, one is to assemble the consultation itself that although it is not something complex, is usually not something usual to do. But the most complicated problem, is that we must establish a "limit" to the number of columns, in your case you have set it to 5, but if you had to expand it you should rewrite the query every time, unless of course, that you transform everything into a dynamic sentence, which also has other problems, but this last one I'll discard for now.

We are going to focus on the output you have requested, for which I am going to ask you a fairly basic solution but it complies with what you ask for. First we create and fill a sample table:

CREATE TABLE #Enfermedad_Paciente_FichaIOS(
    Id_FichaIOS         INT,
    Id_Paciente         INT,
    Fecha               DATETIME,
    Fecha_Enfermedad    DATETIME,
    Id_Tipo_Enfermedad  INT,
    Id_Enfermedad       INT
)

INSERT INTO #Enfermedad_Paciente_FichaIOS (Id_FichaIOS,Id_Paciente,Fecha,Fecha_Enfermedad,Id_Tipo_Enfermedad,Id_Enfermedad)
VALUES  (213,        9607,        '2017-06-27 12:02:36.030', '2017-05-24 00:00:00.000', 3,          13521),
        (213,        9607,        '2017-06-27 12:03:48.540', '2017-05-24 00:00:00.000', 3,                  68),           
        (213,        9607,        '2017-06-27 12:04:52.200', '2017-05-24 00:00:00.000', 1,                  26),           
        (213,        9607,        '2017-06-27 12:07:13.380', '2017-05-24 00:00:00.000', 2,                  2),            
        (213,        9607,        '2017-06-27 12:09:24.630', '2017-06-07 00:00:00.000', 1,                  22),           
        (199,        9560,        '2017-06-26 14:21:54.743', '2017-05-11 00:00:00.000', 2,                  6),            
        (199,        9560,        '2017-06-26 14:24:43.243', '2017-05-15 00:00:00.000', 1,                  20),           
        (199,        9560,        '2017-06-26 14:25:47.050', '2017-05-31 00:00:00.000', 2,                  2)

This simply by taking advantage of the data you have indicated. Now let's see the query:

SELECT  ENF.Id_FichaIOS,
    MAX(CASE WHEN ENF.Nro = 1 THEN ENF.Id_Enfermedad ELSE NULL END) AS 'Enfermedad1',
    MAX(CASE WHEN ENF.Nro = 2 THEN ENF.Id_Enfermedad ELSE NULL END) AS 'Enfermedad2',
    MAX(CASE WHEN ENF.Nro = 3 THEN ENF.Id_Enfermedad ELSE NULL END) AS 'Enfermedad3',
    MAX(CASE WHEN ENF.Nro = 4 THEN ENF.Id_Enfermedad ELSE NULL END) AS 'Enfermedad4',
    MAX(CASE WHEN ENF.Nro = 5 THEN ENF.Id_Enfermedad ELSE NULL END) AS 'Enfermedad5'
    FROM (  SELECT  Id_FichaIOS,
            Id_Enfermedad,
            ROW_NUMBER() OVER (PARTITION BY Id_FichaIOS ORDER BY Id_Enfermedad) AS Nro
            FROM #Enfermedad_Paciente_FichaIOS
        ) ENF
    GROUP BY ENF.Id_FichaIOS

The exit:

Id_FichaIOS Enfermedad1 Enfermedad2 Enfermedad3 Enfermedad4 Enfermedad5
----------- ----------- ----------- ----------- ----------- -----------
199,00      2,00        6,00        20,00        NULL       NULL
213,00      2,00        22,00       26,00        68,00      13.521,00

What we do first with this:

SELECT  Id_FichaIOS,
            Id_Enfermedad,
            ROW_NUMBER() OVER (PARTITION BY Id_FichaIOS ORDER BY Id_Enfermedad) AS Nro
            FROM #Enfermedad_Paciente_FichaIOS

It is to number each disease by Id_FichaIOS , then in the SELECT final% grouped by the same field and we do:

MAX(CASE WHEN ENF.Nro = 1 THEN ENF.Id_Enfermedad ELSE NULL END) AS 'Enfermedad1'

That simply recovers the disease that we have already numbered as 1, the added function allows us to achieve the final grouping.

Setting number of columns dynamically

I add this to complement the answer a bit. The idea is how to define the number of columns we want dynamically. There are not many options, it is more calculated that this is the only way. Let's see

First, we have a table that establishes the number of columns according to the number of records, something like this:

CREATE TABLE #ValoresPorTabla(
    Conteo  INT
)   

INSERT INTO #ValoresPorTabla (Conteo)
VALUES  (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)

With this table, you must create a dynamic statement, and then execute it with sp_executesql :

DECLARE @SQL    NVARCHAR(MAX)

SELECT @SQL = 'SELECT   ENF.Id_FichaIOS,' 
SELECT @SQL = @SQL + CHAR(10) + '        MAX(CASE WHEN ENF.Nro = ' + CONVERT(VARCHAR,T.Conteo) + ' THEN ENF.Id_Enfermedad ELSE NULL END) AS ''Enfermedad' + CONVERT(VARCHAR,T.Conteo) + ''','
        FROM #ValoresPorTabla T

SELECT @SQL = LEFT(@SQL,LEN(@SQL) - 1)

SELECT @SQL = @SQL + CHAR(10) + '
    FROM (  SELECT  Id_FichaIOS,
            Id_Enfermedad,
            ROW_NUMBER() OVER (PARTITION BY Id_FichaIOS ORDER BY Id_Enfermedad) AS Nro
            FROM #Enfermedad_Paciente_FichaIOS
        ) ENF
    GROUP BY ENF.Id_FichaIOS
'
PRINT @SQL

exec sp_executesql @SQL

NOTE : The other possibility is that #ValoresPorTabla have a single record with the number of columns, in this case what varies is that we must use a cycle WHILE that goes from 1 to Conteo with which we complete each of the columns of the query, but the idea is the same.