Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have two tables, GuestList and CustomerList. I joined them and used dynamic SQL pivot table to convert the 'city' column from GuestList table into rows or table headers and the average population would be displayed under each city. So after executing the query at the bottom, my table looks like this and the average population is displayed under each city.

C#
Time   | Atl_1  | Atl_2  | Atl_3 | NY_1 | NY_2
02/12  | 300    |120     |550    | 548  | 784
02/12  | 140    |100     |720    | 369  | 985

I also have an option to sum all the cities' population in ASP.Net. So how do I add all total population of the cities and show like this?

NOTE: My cities name will be dynamic. So the user can select any city from ASP.Net drop down list option so the sum also has to be dynamic instead of adding manually for each city since we don't know which city the user is going to choose.

C#
Time   | Atl_ALL  | NY_ALL  
02/12  |970      |1332    
02/13  | 960      |1354


What I have tried:

Below is the dynamic sql query:

C#
declare @ColumnNames nvarchar(max) = ''
declare @SQL nvarchar(max) = ''

select @ColumnNames += QUOTENAME(a.address) + ','
from GuestList as a
inner join CustomerList as b
on a.Id = b.Id
group by a.address
order by a.address 

set @ColumnNames = left(@ColumnNames, LEN(@ColumnNames)-1  )

set @SQL= N'
select Time,' + @ColumnNames + '
from 
(
select a.Time, a.city, a.population, b.Gender
from GuestList as a
inner join CustomerList as b
on a.Id = b.Id
inner join Split(@city, '','') as c
on a.city = c.Data
where a.city = c.Data
) as SourceTable
pivot
(avg(population) 
for city 
in (' 
    + @ColumnNames + 
    ')) as PivotTable
order by Time'


execute sp_executesql @SQL,
                        N'@city nvarchar(max)'
                        ,@city = @city
Posted
Updated 9-Aug-18 18:20pm
Comments
RedDk 9-Aug-18 14:08pm    
quote: "I joined them" ...

So these numbers are totals per each (column) "address"?

And customers are the same thing as guests? I don't understand.
Member 13863605 9-Aug-18 14:13pm    
Well it's you who is trying to make it difficult! This is t-sql not english literature that you are trying to say customers are the same thing as guests. I said clearly that these two are different tables!!!
Santosh kumar Pithani 10-Aug-18 0:28am    
Why your using "a.address" and "a.city" for pivot column list ?

1 solution

SQL
---first export pivot records into ## global table(i used #temp for showing purpose)
--i have used String_split() instead of your Split() function.. 

CREATE TABLE #TEMP(Time varchar(50),Atl_1  int, Atl_2  int, Atl_3 int, NY_1 int, NY_2 int)
INSERT INTO #TEMP
VALUES
('02/12'  , 300,120 ,550, 548, 784)
,('02/13'  , 140,100 ,720, 369, 985);

DECLARE @query nvarchar(max),@list varchar(max)='Atl_1,Atl_2,Atl_3,NY_1,NY_2';

WITH CTE AS(
  select DISTINCT 
        Stuff((select DISTINCT '+SUM('+value+')' from string_split(@list,',') as dd where dd.value like '%'+substring(ss.value,1,charindex('_',ss.value))+'%'  for xml path('')),1,1,'')+' AS '+substring(ss.value,1,charindex('_',ss.value))+'ALL' as  pcols FROM   string_split(@list,',') as ss
           )
SELECT @list=Stuff((select ','+Pcols from CTE for XML Path('')),1,1,'')

SET @query=N'select time,'+@list+' from #TEMP GROUP BY time';

EXEC(@query);
 
Share this answer
 
v2
Comments
Member 13863605 10-Aug-18 3:51am    
NOTE: My cities name will be dynamic. So the user can select any city from ASP.Net drop down list option so the sum also has to be dynamic instead of adding manually for each city since we don't know which city the user is going to choose.

So your one I have to hard code which I can't. There are like hundred cities. I can't hard code for 100 cities. Please provide a solution which would be dynamic rather than hard coding it.
Santosh kumar Pithani 10-Aug-18 5:33am    
Hello, i have updates new solution so please check and let me know.
Member 13863605 10-Aug-18 8:41am    
Hi,
You are still hard coding the cities! It has to be dynamic. By dynamic I mean there are hundred cities for examples and the user can choose any of them. E.g. if the user chooses Atl, then we will display all the sum of all the values of Atl, if the user selects Atl and NY, the we will display all the sum of all the values of Atl and NY and if the user selects Atl,Chicago and NY, the we will display all the sum of all the values of Atl, Chicago and NY.
So you are hard coding the cities in the temp table. That won't work because we don't know which city the user is going to choose.
Santosh kumar Pithani 10-Aug-18 8:55am    
Have you executed query ?Use @ColumnNames instead of @list.
set @SQL= N'
select Time,' + @ColumnNames + ' INTO ##temp
from ...etc.After this,use if condition

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900