June 4, 2014 at 10:10 am
Hi
I am trying to pivot data based on columns value in year column... but results are not showing up correctly..
I want to see all columns after pivot
I want to Pivot based on year shown in the data but it can be dynamic as year can go for last 3 years
I am also using an inner join as i have two amount columns in my code and i want to show both amount columns for all displayed year...
I am able to pivot but I need in output all the columns like this
Id,MainDate, Year1,Year2,Year3(if any), AMT1 for YR1, AMT2 for Yr1, , AMT1 for YR2, AMT2 for Yr2, AMT1 for YR3, AMT2 for Yr3,
Here is some data:
-- CREATE TABLE [dbo].[TEMP](
--[FileType] [varchar](19) NOT NULL,
--[dType] [char](2) NOT NULL,
--[dVersion] [char](2) NOT NULL,
--[Id] [char](25) NOT NULL,
--[MainDate] [char](40) NULL,
--[Year] [char](30) NULL,
--[AMT1] [char](30) NULL,
--[AMT2] [char](30) NULL
--)
--SELECT * FROM [TEMP]
-- INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000001 ', 'Mar 31, 2014 ', '2013 ', '41600.00 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000001 ', 'Mar 31, 2014 ', '2014 ', '52464.93 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000002 ', 'Mar 31, 2014 ' , '2013 ', '0.00 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000002 ', 'Mar 31, 2014 ', '2014 ', '21924.00 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000003 ', 'Mar 31, 2014 ', '2013 ', '122.00 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000003 ', 'Mar 31, 2014 ', '2014 ', '133.53 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000004 ', 'Mar 31, 2014 ', '2013 ', '8.00 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000004 ', 'Mar 31, 2014 ', '2014 ', '270.00 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000005 ', 'Mar 31, 2014 ', '2013 ', '715.00 ', '2900.00 ')
--INSERT INTO TEMP ( FileType,dType,dVersion,Id,MainDate,Year,AMT1,AMT2) VALUES ( 'ABC', '05', '01', '09000005 ', 'Mar 31, 2014 ','2014 ', '175.00 ', '2900.00 ')
DECLARE @cols AS NVARCHAR(MAX),
@query AS NVARCHAR(MAX);
SET @cols = STUFF(
(SELECT distinct ',' + QUOTENAME(c.Year)
FROM TEMP c
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') ,1,1,''
)
--select @cols
select * from
(
SELECT * from
(
select dType,dVersion,Id
, AMT1,AMT2
, Year
from TEMP
) x
pivot
(
max(AMT1)
for Year in ( [2013 ],[2014 ] )
) p
) A
inner join
(
SELECT * from
(
select dType,dVersion,Id
, AMT2
, Year
from TEMP
) x
pivot
(
max(AMT2)
for Year in ( [2013 ],[2014 ] )
) p
)B
ON A.Id = B.Id
any help on this?
Thanks for your help
Thanks [/font]
June 4, 2014 at 10:31 am
This will generate the dynamic pivot for any number of years.
Read more about it in the following article: http://www.sqlservercentral.com/articles/Crosstab/65048/
DECLARE @Years nvarchar(4000) = '',
@Amt1 nvarchar(4000) = '',
@Amt2 nvarchar(4000) = '',
@SQL nvarchar(MAX) ;
--Generate column lists
SELECT
@Years = @Years + ',MAX( CASE WHEN Year = ' + CAST( Year AS char(4)) + ' THEN Year END) AS Year_' + CAST( Year AS char(4)) + CHAR(13)
,@Amt1 = @Amt1 + ',MAX( CASE WHEN Year = ' + CAST( Year AS char(4)) + ' THEN AMT1 END) AS AMT1_' + CAST( Year AS char(4)) + CHAR(13)
,@Amt2 = @Amt2 + ',MAX( CASE WHEN Year = ' + CAST( Year AS char(4)) + ' THEN AMT2 END) AS AMT2_' + CAST( Year AS char(4)) + CHAR(13)
FROM [TEMP]
GROUP BY Year
ORDER BY Year;
--Create complete SQL Statement
SET @SQL = 'SELECT dType,dVersion,Id' + CHAR(13) + @Years + @Amt1 + @Amt2
+ 'FROM [TEMP] GROUP BY dType,dVersion,Id ';
PRINT @SQL; --Test
EXEC sp_Executesql @SQL --Execute
June 4, 2014 at 11:43 am
Wow...
Works like a gem...
Thanks a lot...
but one question..
as I am running the statement inside a S tored procedure and it will populate a temp table in my SP..
Will the EXEC statement work inside SP too???
Thanks [/font]
June 4, 2014 at 11:50 am
A stored procedure (yours) can call another stored procedure (sp_executesql) without a problem.
Do you want to insert the results on a temp table? That would cause more difficulties but it can be done.
Do you want to insert data into a temp table to pivot it afterwards? yes, that shouldn't cause any trouble.
June 4, 2014 at 11:56 am
Hey Thanks...
Let me modify my SP and give it a Try...
Will comment soon..
Thanks again
Thanks [/font]
June 10, 2014 at 8:42 am
Hi
I was able to work with the code you provided for dynamic pivot successfully,inside my SP using a temp table to store the results using INTO statement
like this:
(MY_TEMP07)
--Create complete SQL Statement
SET @SQL = 'SELECT dType,dVersion,Id' + CHAR(13) + @Years + @Amt1 + @Amt2
+ 'INTO MY_TEMP07 FROM [TEMP] GROUP BY dType,dVersion,Id ';
but today while testing it failed as for one ID's has got only Year 2014 data and nothing for previous 2 years..(so temp table did not create previous 2 years columns)
So my insert statement below if giving me error:
INSERTINTOMAINTABLE
(dType
,dVersion
,Id
,maindate
, PARA1_YEAR
,PARA2_YEAR
,PARA3_YEAR
,PARA1_AMT1
,PARA1_AMT2
,PARA2_AMT1
,PARA2_AMT2
,PARA3_AMT1
,PARA3_AMT2
)
select dType
,dVersion
,Id
,maindate
,coalesce([Year_2014],'')
,coalesce([Year_2013],'')
,coalesce([Year_2012],'')
,coalesce([AMT1_2014],'')
,coalesce([AMT2_2014],'')
,coalesce([AMT1_2013],'')
,coalesce([AMT2_2013],'')
,coalesce([AMT1_2012],'')
,coalesce([AMT2_2012],'')
FROM MY_TEMP07
so my insert inside SP is giving me this error...
Msg 207, Level 16, State 1, Line 1582
Invalid column name 'Year_2013'.
Msg 207, Level 16, State 1, Line 1582
Invalid column name 'Year_2013'.
Msg 207, Level 16, State 1, Line 1588
Invalid column name 'AMT1_2013'.
Msg 207, Level 16, State 1, Line 1588
Invalid column name 'AMT1_2013'.
Msg 207, Level 16, State 1, Line 1589
Invalid column name 'AMT2_2013'.
Msg 207, Level 16, State 1, Line 1589
Invalid column name 'AMT2_2013'.
Is there anyway I can hard code AMT and YR column names generated from this query..
I want only last 3 years 2012..2014..2013..
I was also thinking of creating a dummy table with all columns(YR2014..YR2013...YR2012...AMT..) and inserting data whatever i got from dynamic query to it first then using that table in my final inserts
Any help
Thanks
Thanks [/font]
Viewing 6 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy