Question about 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)

Question about PIVOT

Author

Topic

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-15 : 10:45:50

I've not used PIVOT yet but have a situation where I need to (I think). I'm not fully understanding it after looking it up. Here is my situation:

I have a table Table1 which looks like this:

Article YR YR_STATUS
1 2001 1
1 2002 0
1 2003 1
2 2001 0
2 2002 1
2 2003 1

What I need is:

Article 2001 2002 2003
1 1 0 1
2 0 1 1

Note that there are actually 12 years for each article and not 3. Also every year a new year is added and the oldest year is deleted. So in this example 2004 would be added and 2001 would be deleted next year. So it sort of needs to be dynamic in that sense (no hardcoding years). I know this has to be simple to do. Could someone help me out please? I think that once I see the exact query and PIVOT use for this situation, I will understand it much better.

Thanks,

Van

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 11:23:50

dynamic sql ?

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

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-15 : 11:33:10

Maybe. But I'm having trouble even understanding PIVOT. I can't get it to work even with the example above with a set of 3 years. I keep getting a syntax error near "for". Compatiblity level is 90.

Trying these right now and keep getting that same error:

SELECT *
FROM [CTM_ARTICLE_YEAR] a
pivot (YR_STATUS for yr in ([[2001], [2002], [2003]))

SELECT Article, [2001], [2002], [2003]
FROM [CTM_ARTICLE_YEAR] a
pivot (YR_STATUS for yr in ([2001], [2002], [2003]))

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2007-11-15 : 11:34:23

quote:
Originally posted by khtan

dynamic sql ?

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

Hello,
I wonder if this is similar to my other post about pivot:
Here it is again:

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
...

This is what I have but I still get an error: (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:36:24

[code]DECLARE @sample TABLE
(
Article int,
YR int,
YR_STATUS int
)
INSERT INTO @sample
SELECT 1, 2001, 1 UNION ALL
SELECT 1, 2002, 0 UNION ALL
SELECT 1, 2003, 1 UNION ALL
SELECT 2, 2001, 0 UNION ALL
SELECT 2, 2002, 1 UNION ALL
SELECT 2, 2003, 1

SELECT Article, [2001], [2002], [2003]
FROM @sample
pivot
(
MAX(YR_STATUS) FOR YR IN ([2001], [2002], [2003])
) p[/code]

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

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-15 : 11:41:37

Thank you. I was getting close. I'll work with this and see what I can do.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-15 : 11:43:46

quote:
Originally posted by arkiboys

Hello,
I wonder if this is similar to my other post about pivot:
Here it is again:

Yes. It is. And I have just replied to that post. http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92743

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

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-15 : 11:46:10

SELECT article,
[2001],[2002],[2003]
FROM (Select article, yr, YR_STATUS from
test1) as SourceTable
PIVOT (
SUM(YR_STATUS)
FOR yr IN ([2001], [2002], [2003])
) AS PivotResult

USE tempDB
GO

create table test1 (
Article int, YR varchar(10), YR_STATUS int)

insert into test1 values(1,'2001', 1)
insert into test1 values(1,'2002', 0)
insert into test1 values(1 ,'2003', 1)
insert into test1 values(2 ,'2001', 0)
insert into test1 values(2 ,'2002', 1)
insert into test1 values(2 ,'2003', 1)

Hommer
Aged Yak Warrior

808 Posts

Posted - 2007-11-15 : 12:00:40

By the way, the trick here is that you have to have an aggregate function, EVEN in your case there is no need for it. My post used SUM and KHtan used MAX.

Without it, you get the syntax error near "For"

Van
Constraint Violating Yak Guru

462 Posts

Posted - 2007-11-15 : 12:19:35

You all were right. I got it to work then used dynamic sql to construct the field list for future changes. They actually want it to go into a table and replace the table with the new field names (new years added) each time it's ran (yearly). So I drop the table first then do a SELECT INTO ftw. I was missing the need for the aggregate function and didn't realize it was causing the error I was getting. As it turned out, YR_STATUS was a varchar field with "D" or "I" as the value and not 1 or 0. Luckily there's a PK on ARTICLE and YR so I don't have to worry about an ARTICLE having more than one status for a given year.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources