How do I get group by fields as column name? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How do I get group by fields as column name?

Author

Topic

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-03-10 : 07:22:15

Dear experts,

Hi, please do give me your expert advise and opinon on this matter:

I have a table name PerformaceRecords with a few columns, one of which is performance banding.
i.e.
PerformanceBanding
------------
Outstanding
Good
Average
Good
Poor

When I use a group by clause, i.e. Select PerformanceBanding, Count(PerformanceBanding) as ResultCount from PerformanceRecords group by PerformanceBanding

I got the result as

PerformanceBanding ResultCount
---------------------------------
Good 2
Poor 1
Average 1
Outstanding 1

What I want to get is the PerformanceBanding as columns and the Result as rows

i.e.

Good Poor Average Outstanding
----------------------------------
2 1 1 1

how do I go about modifying my SQL select statement to achieve this result?

Thank you in advance for assisting me.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 07:23:54

are you using SQL 2000 or 2005 ?

in 2005, you can use the PIVOT operator

KH
[spoiler]Time is always against us[/spoiler]

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-03-10 : 07:32:39

SQL 2005. Please can you tell me how do I use the PIVOT operator?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 07:45:06

[code]DECLARE @sample TABLE
(
PerformanceBanding varchar(20)
)

INSERT INTO @sample
SELECT 'Outstanding' UNION ALL
SELECT 'Good' UNION ALL
SELECT 'Average' UNION ALL
SELECT 'Good' UNION ALL
SELECT 'Poor'

SELECT [Good], [Poor], [Average], [Outstanding]
FROM @sample s
pivot
(
COUNT(PerformanceBanding)
FOR PerformanceBanding IN ([Good], [Poor], [Average], [Outstanding])
) p

/*
Good Poor Average Outstanding
----------- ----------- ----------- -----------
2 1 1 1

(1 row(s) affected)
*/[/code]

Check the Books On Line on more information on PIVOT
http://msdn2.microsoft.com/en-us/library/ms177410.aspx

KH
[spoiler]Time is always against us[/spoiler]

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-10 : 07:55:22

Hi,

Try with this also

CREATE TABLE #Temp (PerfName varchar(1000), cnt int)
INSERT INTO #Temp
SELECT 'Good', 2 UNION ALL
SELECT 'Poor', 1 UNION ALL
SELECT 'Average', 1 UNION ALL
SELECT 'Outstanding', 1

SELECT * FROM #Temp

Declare @Sql Varchar(8000),@Sql1 Varchar(8000)
Set @sql = ''
DEclare @str Varchar(8000)
Set @str = ''

Select @sql = @sql + ', Min(Case when PerfName = ''' +PerfName + ''' Then cnt End ) AS "' + PerfName + '"'
From (Select distinct PerfName From #Temp)a
Select @sql = stuff(@sql,1,1,'')

Select @str = @str + 'Select '+@sql+' From #Temp '
print @str
Exec (@str)

DROP TABLE #Temp

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-03-10 : 08:03:15

ranganath, I am afraid I am lost. What are you trying to do in here?

Declare @Sql Varchar(8000),@Sql1 Varchar(8000)
Set @sql = ''
DEclare @str Varchar(8000)
Set @str = ''

Select @sql = @sql + ', Min(Case when PerfName = ''' +PerfName + ''' Then cnt End ) AS "' + PerfName + '"'
From (Select distinct PerfName From #Temp)a
Select @sql = stuff(@sql,1,1,'')

Select @str = @str + 'Select '+@sql+' From #Temp '
print @str
Exec (@str)

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-10 : 08:08:44

ok

it is converting rows to column Dynamically

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-03-10 : 08:09:46

ranganath's solution uses a temp table.

CREATE TABLE #Temp (PerfName varchar(1000), cnt int)

First, you insert the result of your query into the temp table


insert into #Temp(PerfName, cnt)
Select PerformanceBanding, Count(PerformanceBanding) as ResultCount 
from   PerformanceRecords 
group by PerformanceBanding

Then you use the rest of the codes to obtain the result that you want.

KH
[spoiler]Time is always against us[/spoiler]

ranganath
Posting Yak Master

209 Posts

Posted - 2008-03-10 : 08:14:53

Hi,

Actually i forgot to Explain in the post. but KH Explained correctly

Thanks Khtan

EugeneLim11
Posting Yak Master

167 Posts

Posted - 2008-03-10 : 21:32:55

Thank you. Appreciate it.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources