pivot - 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)

pivot

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-15 : 11:00:09

Hi,
I would like to have a sql to show the records as shown at the bottom of the post from this table
Please note that I have only shown a few records here so the sql has to be flexible to work out all the records and transfer the source_Code into columns as you see here.
Thank you

creatre table tblData
(
Security_ID int,
Source_Code varchar(50),
Source_Security_Name varchar(50)
)

insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(19,'EUROVCSC11','MINFIN 7*')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(19,'NFBE','MF7')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(19,'QLABND1','MINFIN 7')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(19,'TR73','MINFIN 7')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(19,'TTFN03','MINFIN 7')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(19,'4GCW','MIN FIN 7')

insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(20,'EUROVCSC11','Alpha 08')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(20,'NFBE','Alpha 08 $')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(20,'QLABND1','Alpha 08 7')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(20,'TR73','Alpha 08 7')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(20,'TTFN03','Alpha 7')
insert into tblData(Security_ID,Source_Code,Source_Security_Name) values(20,'4GCW','Alpha 07')
...

i.e. tblResult

Security_ID EUROVCSC11 NFBE QLABND1 TR73 TTFN03 4GCW
19 MINFIN 7* MF7 MINFIN 7 MINFIN 7 MINFIN 7 MIN FIN 7
20 Alpha 08 Alpha 08 $ Alpha 08 7 Alpha 08 7 Alpha 7 Alpha 07
...

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 11:04:12

use the PIVOT operator

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

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-15 : 11:05:09

quote:
Originally posted by khtan

use the PIVOT operator

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

Hi,
I am trying to use pivot and this is what I have but the error is:
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'for'.

select
VTB_Security_ID,
Source_Code,
Source_Security_Name
from
tblSources
pivot
(
Source_Security_Name for Source_Code in ('EUROVCSC11','NFBE','TTFN03','GFI01','QLABND1','TR73','4GCW')
) as s

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 11:41:21

your column name used in your query is diff from the posted sample data. I had to follow the column name in the sample data posted.

SELECT	Security_ID, [EUROVCSC11], [NFBE], [TTFN03], [GFI01], [QLABND1], [TR73], [4GCW]
FROM	tblData
	pivot
	(
		MAX(Source_Security_Name)
		FOR Source_Code IN ([EUROVCSC11], [NFBE], [TTFN03], [GFI01], [QLABND1], [TR73], [4GCW])
	) AS s

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources