Error Query SQL multiple select

, , , ,
1

Hello everyone

I have a problem with the following query

SELECT (SELECT Dato FROM Dato_Medidor WHERE Linea = 'ELFO_006_Oficinas') as 'ELFO_006',
(SELECT Dato FROM Dato_Medidor WHERE Linea = 'ELFO_007_Oficinas') as 'ELFO_007',
(SELECT Dato FROM Dato_Medidor WHERE Linea = 'ELFO_008_Oficinas') as 'ELFO_008',
(SELECT Dato FROM Dato_Medidor WHERE Linea = 'ELFO_009_Oficinas') as 'ELFO_009',
(SELECT Dato FROM Dato_Medidor WHERE Linea = 'ELFO_010_Oficinas') as 'ELFO_010',
Fecha
FROM Dato_Medidor where CONVERT (DATE,Fecha) = CONVERT (DATE,GETDATE ())

it returns the following error

> Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

I want to divide the data that comes from the "Data" field into columns

2

Since each sub-SELECT for each Linea value returns more than 1 row, it can't be presented correctly as you've written it. The following should give you the expected results:

SELECT MAX(CASE WHEN Linea='ELFO_006_Oficinas' THEN Dato END) AS [ELFO_006]
,MAX(CASE WHEN Linea='ELFO_007_Oficinas' THEN Dato END) AS [ELFO_007]
,MAX(CASE WHEN Linea='ELFO_008_Oficinas' THEN Dato END) AS [ELFO_008]
,MAX(CASE WHEN Linea='ELFO_009_Oficinas' THEN Dato END) AS [ELFO_009]
,MAX(CASE WHEN Linea='ELFO_010_Oficinas' THEN Dato END) AS [ELFO_010]
FROM Dato_Medidor 
WHERE Linea IN('ELFO_006_Oficinas','ELFO_007_Oficinas','ELFO_008_Oficinas','ELFO_009_Oficinas','ELFO_010_Oficinas')
AND Fecha>=CONVERT(DATE,GETDATE ()) AND Fecha<CONVERT(DATE,GETDATE()+1)

I changed the WHERE clause to make the date comparisons more efficient.