Sql Query summerize the detail table

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)

Sql Query summerize the detail table

Author

Topic

smartsys
Starting Member

1 Post

Posted - 2008-01-15 : 15:12:31

I have detail table like

date item_id grade in out
------ ------------- ------- ------ --------
01-01-08 001 A 10 0
02-01-08 001 O 8 0
01-02-08 002 O 1 0
03-01-08 001 T 0 10
02-01-08 003 O 20 0
02-01-08 003 T 0 10
02-01-08 003 B 0 8

Result View
=======

Item_id A B O T Total
------- --- ---- --- --- -------
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 -8 20 -10 2

where grade could be any Alphabet, the column of result query could varies
each grade contains sum of (in-out) of all item_id of detail table.

Smartsys

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-15 : 16:02:39

IF OBJECT_ID('tempdb..##a') IS NOT NULL
DROP TABLE ##a

CREATE TABLE ##a (
date DATETIME,
item_id CHAR(3),
grade CHAR(1),
[IN] INT,
[out] INT )

INSERT ##a ( date, item_id, grade, [in], [out] )
SELECT'01-01-08', '001', 'A', 10, 0 UNION ALL
SELECT'02-01-08', '001', 'O', 8, 0 UNION ALL
SELECT'01-02-08', '002', 'O', 1, 0 UNION ALL
SELECT'03-01-08', '001', 'T', 0, 10 UNION ALL
SELECT'02-01-08', '003', 'O', 20, 0 UNION ALL
SELECT'02-01-08', '003', 'T', 0, 10 UNION ALL
SELECT'02-01-08', '003', 'B', 0, 8

DECLARE @gradelist1 VARCHAR(1000), @gradelist2 VARCHAR(1000), @gradelist3 VARCHAR(1000), @sql VARCHAR(max)
SELECT @gradelist1 = coalesce(@gradelist1+', ['+grade+']', '['+grade+']', @gradelist1) FROM (SELECT DISTINCT grade FROM ##a) a order by grade
SELECT @gradelist2 = coalesce(@gradelist2+', ISNULL(['+grade+'], 0) AS '+grade, 'ISNULL(['+grade+'], 0) AS '+grade, @gradelist2) FROM (SELECT DISTINCT grade FROM ##a) a order by grade
SELECT @gradelist3 = coalesce(@gradelist3+'+ISNULL(['+grade+'], 0)', 'ISNULL(['+grade+'], 0)', @gradelist3) FROM (SELECT DISTINCT grade FROM ##a) a order by grade
SELECT @gradelist3 = @gradelist3+' AS Total'

SELECT @sql = 'SELECT Item_id, '+@gradelist2+', '+@gradelist3+'
FROM (SELECT Item_id, grade, [in]-[out] AS inout FROM ##a) a
PIVOT (sum([inout]) FOR grade IN ('+@gradelist1+')) AS p'
EXEC (@sql)

"...database development, while a serious pursuit and vitally important to business, should be fun!"
-Adam Machanic

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-15 : 22:03:04

[code]DECLARE @sample TABLE
(
[date] datetime,
item_id varchar(3),
grade CHAR(1),
[IN] int,
[out] int
)
INSERT INTO @sample
SELECT '01-01-08', '001', 'A', 10, 0 UNION ALL
SELECT '02-01-08', '001', 'O', 8, 0 UNION ALL
SELECT '01-02-08', '002', 'O', 1, 0 UNION ALL
SELECT '03-01-08', '001', 'T', 0, 10 UNION ALL
SELECT '02-01-08', '003', 'O', 20, 0 UNION ALL
SELECT '02-01-08', '003', 'T', 0, 10 UNION ALL
SELECT '02-01-08', '003', 'B', 0, 8

SELECT item_id,
[A] = ISNULL([A], 0),
[B] = ISNULL([B], 0),
[O] = ISNULL([O], 0),
[T] = ISNULL([T], 0),
total = ISNULL([A], 0) + ISNULL([B], 0) + ISNULL([O], 0) + ISNULL([T], 0)
FROM
(
SELECT item_id, grade, nett = [IN] - [out]
FROM @sample
) d
pivot
(
SUM(nett)
FOR grade IN ([A], [B], [O], [T])
)p

/*
item_id A B O T total
------- ----------- ----------- ----------- ----------- -----------
001 10 0 8 -10 8
002 0 0 1 0 1
003 0 -8 20 -10 2

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources