Creating a total record from detail

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 2008 Forums

Transact-SQL (2008)

Creating a total record from detail

Author

Topic

sadderson
Starting Member

13 Posts

Posted - 2010-02-06 : 01:30:55

Situation

I have:

open 01 10
open 02 15
open 03 5
open 04 6
close 01 5
close 03 10
close 05 15

I need :

Type 01 02 03 04 05

open 10 15 5 6 0
close 5 0 10 0 15

Each entry needs to be a field so i can create a report, so type 01, 02 03 04 and 05 are fields.

Thanks

Scott

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-02-06 : 02:04:27

[code]
DECLARE @sample TABLE
(
type varchar(5),
col varchar(2),
val int
)
INSERT INTO @sample
SELECT 'open', '01', 10 UNION ALL
SELECT 'open', '02', 15 UNION ALL
SELECT 'open', '03', 5 UNION ALL
SELECT 'open', '04', 6 UNION ALL
SELECT 'close', '01', 5 UNION ALL
SELECT 'close', '03', 10 UNION ALL
SELECT 'close', '05', 15

SELECT *
FROM @sample s
pivot
(
SUM(val)
FOR col IN ([01], [02], [03], [04], [05])
) p

/*
type 01 02 03 04 05
----- ----------- ----------- ----------- ----------- -----------
close 5 NULL 10 NULL 15
open 10 15 5 6 NULL

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

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

sadderson
Starting Member

13 Posts

Posted - 2010-02-06 : 03:04:55

Sure, works like a charm...

Thanks again

Scott right on the money..

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-06 : 06:16:46

if types are dynamic by any case you can use below

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources