Getting data from multiple column as one in SQL Procedure

Question

I'm using SQL Server and I have a table with columns:

Id, State1, State2, State3, City1, City2, City3, Street1, Street2, Street3.

Where State1, City1, Street1 are a "triple".

I need to select all data from the table where

(State1 = xyz, City1 = xyzv, Street1 = abcd) or (State2 = xyz....)

and show it like that: State1 as State/State2 as State -> the same name for each triple columns.

I need to say that each triple in row is unique or null. So there can be 1,2,3 unique addresses in row.

How can I do it? Now I have something like that, but there is an

Msg 116, Level 16, State 1, Line 14 Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. error.

    select ds.Data as Data, 
    case 
    when ds.Gmina1 = @StateName and ds.Miejscowosc1 = @CityName and ds.Street1 = @StreetName and ds.LocalNumber1 = @LocalNumber then (select ds.Gmina1 as Gmina, ds.Miejscowosc1 as Miasto, ds.Street1 as Ulica, ds.LocalNumber1 from Dostawy)
    when ds.Gmina2 = @StateName and ds.Miejscowosc2 = @CityName and ds.Street2 = @StreetName and ds.LocalNumber2 = @LocalNumber then (select ds.Gmina2 as Gmina, ds.Miejscowosc2 as Miasto, ds.Street2 as Ulica, ds.LocalNumber2 from Dostawy)
    when ds.Gmina2 = @StateName and ds.Miejscowosc3 = @CityName and ds.Street3 = @StreetName and ds.LocalNumber3 = @LocalNumber then (select ds.Gmina3 as Gmina, ds.Miejscowosc3 as Miasto, ds.Street3 as Ulica, ds.LocalNumber3 from Dostawy)
    else null
    end
  from dbo.Dostawy as ds

I need to select all data from the table **where**... - perhaps it's better to put those conditions into WHERE clause? — IVNSTN
– IVNSTN, Commented Oct 15, 2018 at 6:02

Zhorov · Accepted Answer · 2018-10-15 06:09:02Z

Another possible approach to get your resultset is to use UNION ALL and appropriate WHERE clause:

SELECT ds.Data AS Data, ds.Gmina1 AS Gmina, ds.Miejscowosc1 AS Miasto, ds.Street1 AS Ulica, ds.LocalNumber1 AS LocalNumber
FROM Dostawy ds
WHERE
    ds.Gmina1 = @StateName AND
    ds.Miejscowosc1 = @CityName AND
    ds.Street1 = @StreetName AND 
    ds.LocalNumber1 = @LocalNumber
UNION ALL
SELECT ds.Data AS Data, ds.Gmina2 AS Gmina, ds.Miejscowosc2 AS Miasto, ds.Street2 AS Ulica, ds.LocalNumber2 AS LocalNumber 
FROM Dostawy ds
WHERE
    ds.Gmina2 = @StateName AND
    ds.Miejscowosc2 = @CityName AND
    ds.Street2 = @StreetName AND 
    ds.LocalNumber2 = @LocalNumber
UNION ALL
SELECT 
    ds.Data AS Data, ds.Gmina3 AS Gmina, ds.Miejscowosc3 AS Miasto, ds.Street3 AS Ulica, ds.LocalNumber3 AS LocalNumber 
FROM Dostawy ds
WHERE 
    ds.Gmina2 = @StateName AND
    ds.Miejscowosc3 = @CityName AND
    ds.Street3 = @StreetName AND 
    ds.LocalNumber3 = @LocalNumber

Vinit · Accepted Answer · 2018-10-15 06:06:04Z

The root cause of the error is the below expression in each THEN block. As error explains, you cannot have multiple columns, so you should select only one.

(select ds.Gmina1 as Gmina, ds.Miejscowosc1 as Miasto, ds.Street1 as Ulica, ds.LocalNumber1 from Dostawy)

Also you need not to have another SELECT sub query to get the value for that one column, the below query should work.

select ds.Data as Data, 
    case 
    when ds.Gmina1 = @StateName and ds.Miejscowosc1 = @CityName and ds.Street1 = @StreetName and ds.LocalNumber1 = @LocalNumber then ds.Gmina1
    when ds.Gmina2 = @StateName and ds.Miejscowosc2 = @CityName and ds.Street2 = @StreetName and ds.LocalNumber2 = @LocalNumber then ds.Gmina2
    when ds.Gmina2 = @StateName and ds.Miejscowosc3 = @CityName and ds.Street3 = @StreetName and ds.LocalNumber3 = @LocalNumber then ds.Gmina3
    else null
    end as Gmina
  from dbo.Dostawy as ds

Collectives™ on Stack Overflow

Getting data from multiple column as one in SQL Procedure

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related