CODEWITHSUNDEEP
sqlsql-server
RobinВricks
RobinВricks

Reputation: 93

Field as a substring

I have a table with similar data, I am looking for a way to combine the [city] and [street] fields into one field. The [street] field was a substring for the [city] field and find the sum across the [city] field Expected result below:

city/street   value1    value2  value3  total
Wien            16        50      6       72
Quadenstrabe    14        46      1       61
Laberlsteg      2         4       5       11
Hamburg         7         9       5       21
Esplanade       3         7       4       14
Drehbahn        4         2       1       7

How to do it in mssql?

CREATE TABLE [dbo].[#CITY_TABLE](
[city] NVARCHAR(50) NULL,
[street] NVARCHAR(50) NULL,
[value1] NVARCHAR(50) NULL,
[value2] NVARCHAR(50) NULL,
[value3] NVARCHAR(50) NULL,
[total] NVARCHAR(50) NULL
) ON [PRIMARY]


insert into [dbo].[#CITY_TABLE] values('Wien','Quadenstrabe','14','46','1','61')
insert into [dbo].[#CITY_TABLE] values('Wien','Laberlsteg','2','4','5','11')
insert into [dbo].[#CITY_TABLE] values('Hamburg','Esplanade','3','7','4','14')
insert into [dbo].[#CITY_TABLE] values('Hamburg','Drehbahn','4','2','1','7')

select * from [dbo].[#CITY_TABLE]

Upvotes: 0

Views: 75

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269793

I think I would do this as:

select v.city_street, sum(t.value1) as value1, sum(t.value2) as value2, sum(t.value3) as value3
from #CityTable ct cross apply
     (values (city), (street)
     ) v(city_street)
group by city_street;

This assumes that the numbers are really stored as numbers rather than strings.

Upvotes: 1

Tim Kaiser
Tim Kaiser

Reputation: 76

Your life would be much easier if Value1, Value2, Value3, and Total were datatype int. But, with what you have:

SELECT 
    CITY [City/Street],
    SUM(convert(int,VALUE1)) [Value1],
    SUM(convert(int,VALUE2)) [Value2],
    SUM(convert(int,VALUE3)) [Value3],
    SUM(convert(int,TOTAL)) [Value1] from 
[dbo].#CITY_TABLE GROUP BY CITY
UNION
SELECT 
    STREET, 
    convert(int,value1), 
    convert(int,value2), 
    convert(int,value3),
    convert(int,total) 
from [dbo].#CITY_TABLE

Upvotes: 2

Thom A
Thom A

Reputation: 95561

Provided you fix your datatype and don't use an nvarchar to store an int, this works:

SELECT ISNULL(street, city) AS CityStreet,
       SUM([Value1]) AS Value1,
       SUM([Value2]) AS Value2,
       SUM([Value3]) AS Value3,
       SUM(Total) AS Total
FROM #CITY_TABLE
GROUP BY city, street WITH ROLLUP
HAVING city IS NOT NULL
ORDER BY CityStreet DESC;

Upvotes: 7

Related Questions