November 7, 2005 at 4:27 am
I have a table in the following format:
headings: code year1 year2 year3
values: A1 250.00 null null
A1 null 150.00 null
A1 null null 180.00
I want to create a summarised table with one single row: I.E
A1 250.00 150.00 180.00
Another problem is that I never know how many years of account there are, so the ability to have a dynamic query would be best for me.
Any help or suggestions would be gratefully received.
November 7, 2005 at 6:17 am
SET NOCOUNT ON
DECLARE @MyAccts TABLE
(
Account VARCHAR(10),
Year1 NUMERIC(10,2) NULL,
Year2 NUMERIC(10,2) NULL,
Year3 NUMERIC(10,2) NULL
)
INSERT @MyAccts
SELECT 'A1', 250.00, null, null UNION
SELECT 'A1', null, 150.00, null UNION
SELECT 'A1', null, null, 180.00
SELECT SUM(COALESCE(Year1, 0)) Year1, SUM(COALESCE(Year2, 0)) Year2, SUM(COALESCE(Year3, 0)) Year3
FROM
@MyAccts
Regards,
gova
November 7, 2005 at 6:42 am
/*I would make a design something like this*/
SET NOCOUNT ON
/* Data Table */
CREATE TABLE MyAcctsNew
(
Account VARCHAR(10),
DateYear DATETIME,
Amount NUMERIC(10,2)
)
INSERT MyAcctsNew
SELECT 'A', '01/01/2000', 250.00 UNION
SELECT 'A', '01/01/2001', 150.00 UNION
SELECT 'A', '01/01/2002', 180.00 UNION
SELECT 'A', '01/01/2003', 185.00
/* Solution */
DECLARE @Years TABLE
(
AcctYear VARCHAR(4)
)
INSERT @Years
SELECT DISTINCT CONVERT(VARCHAR, DATEPART(YEAR, DateYear)) FROM MyAcctsNew
DECLARE @SQL VARCHAR(2000)
SELECT @SQL = 'SELECT Account' + CHAR(13)
SELECT @SQL = COALESCE(@SQL + ', ', '') + 'Year' + AcctYear + ' = SUM(CASE WHEN DATEPART(YEAR, DateYear) = ' + AcctYear + ' THEN Amount ELSE 0 END)' + CHAR(13)
FROM
(SELECT TOP 100 PERCENT AcctYear
FROM
@Years
ORDER BY 1) A
SELECT @SQL = @SQL + 'FROM ' + CHAR(13)
SELECT @SQL = @SQL + ' MyAcctsNew ' + CHAR(13)
SELECT @SQL = @SQL + 'GROUP BY Account' + CHAR(13)
EXECUTE(@SQL)
GO
DROP TABLE MyAcctsNew
Regards,
gova
November 7, 2005 at 7:15 am
Thanks, govinn. Just what the doctor ordered.
Simplifies it for me a lot.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply