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://qa.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:
INSERT INTO MAINTABLE
( 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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply