asked
How to convert rows into column and count them group by a particular column?
I have database in which there are two column named 'state' and 'CompanyStatus'.company status value is like paternership,propriotership and other.I wated to show this table like partenership,propriotership,other as a column and count the number of partenership,propriotership and other group by state. initial table is look like:-- State company Status West Bengal parternership Rajshthan Proprietorship West Bengal Proprietorship Rajshthan parternership Jharkhand other West Bengal partenership final table is look like:-- State parternership Proprietorship other Rajshthan 1 1 - Jharkhand - - 1 West bengal 2 1 - [1]: /storage/temp/987-initial.txt [2]: /storage/temp/988-final.txt
Please provide some sample data and an example of how you want the results to look like.
As you can see from the solution below, it relies heavily on the quality of the data you have in the table DECLARE @Table TABLE ([State] varchar(30), [CompanyStatus] varchar(30)) INSERT @Table ([State], [CompanyStatus]) VALUES ('West Bengal', 'parternership'), ('Rajshthan', 'Proprietorship'), ('West Bengal', 'Proprietorship'), ('Rajshthan', 'parternership'), ('Jharkhand', 'other'), ('West Bengal', 'partenership ') SELECT [T].[State], SUM(CASE WHEN [T].[CompanyStatus] = 'parternership' THEN 1 ELSE 0 END) AS [parternership], SUM(CASE WHEN [T].[CompanyStatus] = 'Proprietorship' THEN 1 ELSE 0 END) AS [Proprietorship], SUM(CASE WHEN [T].[CompanyStatus] = 'other' THEN 1 ELSE 0 END) AS [other], SUM(CASE WHEN [T].[CompanyStatus] = 'partenership' THEN 1 ELSE 0 END) AS [partenership] FROM @Table AS T [edit] - code edited to include SUM function(s)
In this code where are u counting the number of 'parternership' ,propriotership' and 'other' in a every state...it's not about i have to fill 1 or 0...i have to count number of parternership ,propriotership and other fill as in given in the table using sql queries... please help me...thanks...
14 People are following this question.
