April 16, 2004 at 11:37 am
Hi everybody!
I have this table:
YEAR AGE
-----------
1999 25
1999 35
1999 25
1999 30
1999 35
1999 35
1999 25
1999 25
2000 25
2000 25
2000 25
2000 35
2000 35
2000 35
2000 25
2000 25
2000 25
2001 30
2001 30
2001 30
2001 25
And I would like the result to show like this
1999 2000 2001
25 4 6 1
30 1 0 3
35 3 3 0
IS this possible?
Thanks a lot
April 17, 2004 at 7:51 am
Hi this is not a very clean or optimized solution but should solve your problem from the requirement point of you, This is not an advised methodology to implement as it is pure dynamic SQL and I myself do not personally recommend usage of dynamic SQL.
I assumed that the name of your table holding the data is TEST.
ALTER PROCEDURE TEST1 AS
DECLARE @YEAR1 INT,@AGE INT
DECLARE @YEAR INT
DECLARE @SQL NVARCHAR(1024)
DECLARE @U_SQL NVARCHAR(1024)
CREATE TABLE #TEST (AGE INT )
DECLARE GETYEARS CURSOR FOR
SELECT DISTINCT(YEAR) FROM TEST ORDER BY YEAR ASC
OPEN GETYEARS
FETCH NEXT FROM GETYEARS INTO @YEAR
WHILE @@FETCH_STATUS=0
BEGIN
SET @SQL='ALTER TABLE #TEST ADD ['
+ CONVERT(VARCHAR(4),@YEAR) + '] INT DEFAULT (0)'
EXEC (@SQL)
FETCH NEXT FROM GETYEARS INTO @YEAR
END
CLOSE GETYEARS
DEALLOCATE GETYEARS
INSERT INTO #TEST(AGE) (SELECT DISTINCT(AGE) FROM TEST)
DECLARE GETYEARS1 CURSOR FOR
SELECT DISTINCT(YEAR),AGE FROM TEST GROUP BY AGE,YEAR ORDER BY YEAR
OPEN GETYEARS1
FETCH NEXT FROM GETYEARS1 INTO @YEAR1,@AGE
WHILE @@FETCH_STATUS=0
BEGIN
SET @U_SQL='UPDATE #TEST SET [' + CONVERT (VARCHAR(4), @YEAR1) + '] = (SELECT COUNT(*) FROM TEST WHERE YEAR='+ CONVERT(VARCHAR(4),@YEAR1) + ' AND AGE = ' + CONVERT (VARCHAR(3),@AGE) + ' GROUP BY YEAR ) WHERE AGE = ' + CONVERT(VARCHAR(3),@AGE)
EXEC (@U_SQL)
FETCH NEXT FROM GETYEARS1 INTO @YEAR1,@AGE
END
CLOSE GETYEARS1
DEALLOCATE GETYEARS1
SELECT * FROM #TEST
Thanks
Prasad Bhogadi
www.inforaise.com
April 17, 2004 at 7:59 am
USE CREATE PROCEDURE INSTEAD OF ALTER
THANKS
Prasad Bhogadi
www.inforaise.com
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply