April 30, 2012 at 7:29 am
I have a column called update_source in my table that I want to pivot on. I want to make sure that the value that comes out is the latest value(based on effective_date) and that I display the nra_tax_country column (shows the country). I tried replacing the max(effective_date) in the pivot with the nra_tax_country, but it keeps giving me an error.
Do I need to do a group by in the outer select, and some other way of getting the country into the pivot?
Thanks,
M@
declare @columns VARCHAR(8000)
SELECT
@columns =
COALESCE
(
@columns + ',[' + update_source + ']',
'[' + update_source + ']'
)
FROM
secmaster_history
where update_source not in ('COMP_SEC','sa','TDvorets','DKorets')
GROUP BY
update_source
DECLARE @query VARCHAR(8000)
SET @query = 'SELECT *
FROM
(
SELECT
ticker,
update_source,
effective_date,
nra_tax_country
FROM
secmaster_history
where update_source not in (''COMP_SEC'',''sa'',''TDvorets'',''DKorets'')
) PIV
PIVOT
(
MAX(effective_date) FOR update_source in (' + @columns + ')
) AS chld'
execute(@query)
GO
April 30, 2012 at 7:38 am
Post the error message that you are receiving
Also it would be better if you post some sample data along with the DDL and the expected results
This will help us in providing a tested solution back
Check the link in my signature for more details
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 30, 2012 at 7:45 am
I don't think I can post data from work, against policy, tho I know it'd help immesurably 🙂
When I change the pivot statement to
PIVOT
(
nra_tax_country FOR update_source in (' + @columns + ')
) AS chld'
I just get incorrect syntax near the keyword 'FOR'
I'm getting data that looks like this:
ticker, nra_tax_country, A, B, C
ABC, US, 1/1/1999, NULL, 5/8/2001
DEF, AR, NULL, 6/1/2008, NULL
What I want to do is switch the date and the country so the effective_date is replaced by the state.
April 30, 2012 at 7:49 am
Try changing nra_tax_country to MAX( nra_tax_country ) and see if it works
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
April 30, 2012 at 7:53 am
Okay, that's not fair. I tried that before I posted all of this and got a syntax error.
I think that solved it, thanks man!
April 30, 2012 at 8:16 am
Matthew Cushing (4/30/2012)
Okay, that's not fair. I tried that before I posted all of this and got a syntax error.I think that solved it, thanks man!
It happens sometimes:-)
Glad to help you out.
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply