question

m_don avatar image
m_don 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
database-name
initial.txt (191 B)
final.txt (124 B)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Please provide some sample data and an example of how you want the results to look like.
0 Likes 0 ·
I provided some sample data...so please reply me...thanks
0 Likes 0 ·

1 Answer

·
Fatherjack avatar image
Fatherjack answered
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)
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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...
0 Likes 0 ·
I have amended the code to carry out the sum of the values
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.