simple select Query - SQL Server Forums

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

simple select Query

Author

Topic

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-17 : 06:10:37

hi all

i have table like this

PID	Project	val1
1	A	8
1	A	89
2	C	7
2	A	79
2	A	9
3	B	90
3	A	7
3	C	87
3	A	789
3	A	897
4	B	7
4	A	79
4	B	9
4	B	90
5	C	7
5	B	87
5	B	789
5	B	897
6	C	7
6	C	79
6	C	9
6	A	90
6	C	7
7	A	87
7	B	789
7	C	7

and i want the result like this (sum or count)



PID	A	B	C
1	97		
2	88		7
3	1693	90	87
4	79	106	
5		1773	7
6	90		102
7	87	789	7

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-07-17 : 06:19:04

Are you using SQL 2000 or 2005/2008 ?

For 2005/2008 use the PIVOT operator

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

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2009-07-17 : 06:19:28

Use the PIVOT command.

http://msdn.microsoft.com/en-us/library/ms177410.aspx

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-17 : 06:19:41

SQL 2000

krishna_yess
Yak Posting Veteran

81 Posts

Posted - 2009-07-17 : 06:20:45

SQL 2000 (prod) 2005 (test )

i need for both esp. for 2000

and also can i create a view with group by fucntion

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-17 : 06:34:35

in 2000 u can use dynamic cross tab
and 2005 and above use pivot
see this link
http://sqlblogcasts.com/blogs/madhivanan/archive/2007/08/27/dynamic-crosstab-with-multiple-pivot-columns.aspx

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-07-17 : 06:35:13

[code]SELECT PID, [A], [B], [C]
FROM(
SELECT PID, Project, val1
FROM table
)a
PIVOT
(
SUM(val1) FOR Project IN ([A], [B], [C])
) as pvt

SELECT PID,
SUM(CASE WHEN Project = 'A' then val1 else 0 END)[A],
SUM(CASE WHEN Project = 'B' then val1 else 0 END)[B],
SUM(CASE WHEN Project = 'C' then val1 else 0 END)[C]
FROM table[/code]

Hope can help...but advise to wait pros with confirmation...

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-07-17 : 06:41:21

[code]
first one will work in 2005 and above
second one will work in 2000 ,2005,2008
select pid,[a],[b],[c]
from @tab s
pivot (sum(val1) for project in ([a],[b],[c]))p

select pid,
sum(case when project ='a' then val1 end ) as 'a',
sum(case when project ='b' then val1 end ) as 'b',
sum(case when project ='c' then val1 end ) as 'c'
from @tab
group by pid

sorry toooooooo late
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources