query to show row data in columns

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)

query to show row data in columns

Author

Topic

imughal
Posting Yak Master

192 Posts

Posted - 2009-10-10 : 00:23:10

hi,

i want to show my row data in columns. my table structure is as follows:

Name Field1 Fieldname Date Time
abc 220 P1 7/09/09 9:00
abc 230 P2 7/09/09 9:00
abc 250 P3 7/09/09 9:00

abc 210 P1 7/09/09 9:20
abc 205 P2 7/09/09 9:20
abc 200 P3 7/09/09 9:20

abc 210 P1 7/09/09 9:45
abc 205 P2 7/09/09 9:45
abc 200 P3 7/09/09 9:45

I want to my result as follow

Name P1 P2 p3 Date Time SUM
abc 220 230 250 7/09/09 9:00 700
abc 210 205 200 7/09/09 9:20 615
abc 210 205 200 7/09/09 9:45 615

kindly help me out to write query to produce desire result.

thx

Kabila
Starting Member

33 Posts

Posted - 2009-10-10 : 01:15:54

SELECT Name,
[P1] AS P1, [P2] AS P2,[P3] AS P3,Date,[P1]+[P2]+[P3] as Sum
FROM
(SELECT * from tablename ) ps
PIVOT
(
sum(Field1)
FOR Fieldname IN
( [P1], [P2],[P3]) )AS A

imughal
Posting Yak Master

192 Posts

Posted - 2009-10-10 : 03:28:02

thx, jut little problem my "Field1" data type is string when i converting it to int getting error, pls help me out

SELECT Name,
[P1] AS P1, [P2] AS P2,[P3] AS P3,Date,[P1]+[P2]+[P3] as Sum
FROM
(SELECT * from tablename ) ps
PIVOT
(
sum((CONVERT(int,Field1))
FOR Fieldname IN
( [P1], [P2],[P3]) )AS A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources