How can get Fields as Col Name and count fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How can get Fields as Col Name and count fields

Author

Topic

venkatakondareddy
Starting Member

17 Posts

Posted - 2008-12-27 : 00:54:21

Hi,
Here im having one table IssueStatus with fields IssueStatusId, Name.

IssueStatusId Name
-------------- -------
1 NotStarted
2 Started
3 Hold
4 Stopped
5 Completed
6 NA
7 NotStarted
8 Completed

This is my table. Now What i need id these fields(NotStarted, Started,... ) as Col names
and count of each fied no of times repeated.

output like this
----------------------------------------------------
NotStarted Started Hold Stopped Completed NA
---------- ------- ----- ------- --------- ---
2 1 1 1 2 1

In Oracle for this solution Decode Keyword is there.
Is there any keyword in SqlServer.
Please try to help me.

Regards
-------
Venkat R

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-27 : 01:46:47

SELECT
COUNT(CASE name WHEN 'NotStarted' THEN IssueStatusId ELSE 0 END) AS NotStarted,
COUNT(CASE name WHEN 'Started' THEN IssueStatusId ELSE 0 END) AS Started,
COUNT(CASE name WHEN 'Hold' THEN IssueStatusId ELSE 0 END) AS Hold ,
COUNT(CASE name WHEN 'Stopped ' THEN IssueStatusId ELSE 0 END) AS Stopped ,
COUNT(CASE name WHEN 'Completed ' THEN IssueStatusId ELSE 0 END) AS Completed ,
COUNT(CASE name WHEN 'NA' THEN IssueStatusId ELSE 0 END) AS NA
FROM
IssueStatus

try like this
or use pivot function

venkatakondareddy
Starting Member

17 Posts

Posted - 2008-12-27 : 02:49:22

Thanks bklr,
Its worinkg, but its not giving expected output.
It has given 6 - 6 - 6 - 6 - 6 - 6
thats not right what i wrote my query.
output like 2-1-1-1-2-1

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-27 : 03:56:44

quote:
Originally posted by venkatakondareddy

Thanks bklr,
Its worinkg, but its not giving expected output.
It has given 6 - 6 - 6 - 6 - 6 - 6
thats not right what i wrote my query.
output like 2-1-1-1-2-1

Hi,

This will give your require output

SELECT
SUM(CASE name WHEN 'NotStarted' THEN 1 ELSE 0 END) AS NotStarted,
SUM(CASE name WHEN 'Started' THEN 1 ELSE 0 END) AS Started,
SUM(CASE name WHEN 'Hold' THEN 1 ELSE 0 END) AS Hold ,
SUM(CASE name WHEN 'Stopped ' THEN 1 ELSE 0 END) AS Stopped ,
SUM(CASE name WHEN 'Completed ' THEN 1 ELSE 0 END) AS Completed ,
SUM(CASE name WHEN 'NA' THEN 1 ELSE 0 END) AS NA
FROM
IssueStatus

raky
Aged Yak Warrior

767 Posts

Posted - 2008-12-27 : 04:15:22

If you are using sqlserver 2005 then the below also give ur required output

SELECT pvt.NotStarted , pvt.Started , pvt.Hold , pvt.Stopped, pvt.Completed , pvt.NA
FROM
(SELECT IssueStatusId, Name
FROM IssueStatus) p
PIVOT
(
COUNT (IssueStatusId)
FOR [name] IN
(
NotStarted,
Started,
Hold ,
Stopped,
Completed,
NA
)
) AS pvt

venkatakondareddy
Starting Member

17 Posts

Posted - 2008-12-27 : 05:45:36

thanks raky, It has given expected output. But here i need to map more than one table.
For example here im giving one more table by name Issue
which is having some columns are AssetId, IssueStatusId,..
AssetId--IssueStatusId
------- -------------
2 -----3
i need to map these 2 tabls.
Is it possible by using PIVOT?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-27 : 11:32:53

Its possible just modify the query as


SELECT t.*,t2.NotStarted,t2.Started,...
FROM YourFirstTable t
JOIN (SELECT AssetID,
      SUM(CASE WHEN Name='NotStarted' THEN 1 ELSE 0 END) AS NotStarted,
SUM(CASE WHEN Name='Started' THEN 1 ELSE 0 END) AS Started,
SUM(CASE WHEN Name='Hold' THEN 1 ELSE 0 END) AS Hold,
SUM(CASE WHEN Name='Stopped' THEN 1 ELSE 0 END) AS Stopped..
FROM SecondTable
GROUP BY AssetID)t2
ON t2.AssetID=t.AssetID

venkatakondareddy
Starting Member

17 Posts

Posted - 2008-12-31 : 07:28:18

Thanks for all giving reply.
How can I do this by CTE hierarchical.
Can you help me how to write?
Regards
Venkat R

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-31 : 07:44:14

quote:
Originally posted by venkatakondareddy

Thanks for all giving reply.
How can I do this by CTE hierarchical.
Can you help me how to write?
Regards
Venkat R

whats the need of CTE here?
you dont have any hierarchy of data existing.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources