CrossTab without Agregate - SQL Server Forums

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)

CrossTab without Agregate

Author

Topic

ajiekurnia
Starting Member

1 Post

Posted - 2008-01-20 : 21:49:10

Dear All,

I Have Problem with SQL Command to generate result like this :
Table Structure
===============
TYPEDETAILID
TYPYEAR
TYPEYEARDESC
====================================
TYPDETAILID TYPYEAR TYPEYEARDESC
--------------- ------- ------------
000000003800001 1997 KegiatanA
000000003800001 1998 KegiatanB
000000003800001 1999 KegiatanC

The Report:
1997 1998 1999
000000003800001 KegiatanA KegiatanB KegiatanC

Please Help me to solve this problem. Thx

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-01-21 : 01:34:09

[code]DECLARE @temp table
(
TYPDETAILID int,
TYPYEAR int,
TYPEYEARDESC varchar(100)
)
--------------- ------- ------------
INSERT INTO @temp VALUES(000000003800001, 1997, 'KegiatanA')
INSERT INTO @temp VALUES(000000003800001 ,1998, 'KegiatanB')
INSERT INTO @temp VALUES(000000003800001 ,1999, 'KegiatanC')

SELECT * FROM
(SELECT TYPDETAILID,TYPYEAR,TYPEYEARDESC FROM @temp)d
PIVOT(MAX(TYPEYEARDESC) FOR TYPYEAR IN ([1997],[1998],[1999]))p

output
-------------
TYPDETAILID 1997 1998 1999
----------- -------------------- -------------------- ---------------
3800001 KegiatanA KegiatanB KegiatanC
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources