January 23, 2014 at 9:46 pm
i am trying to pivot table DYNAMICALLY but couldn't get the desired result .
here is the code to create a table
create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
creation int
)
insert into Report values
('A_1',0.345,0,0,1.23,20140212),
('B_2',0.456,0,4,2.34,20140215),
('C_3',0.554,0,6,0.45,20140217),
('D_4',0.231,0,8,7.98,20140222),
('E_5',0.453,0,0,5.67,20140219),
('F_6',0.344,0,3,7.23,20140223)
code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()
DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;
SET @sql = N'
SELECT ' + STUFF(@columns, 1, 2, '') + '
FROM
(
SELECT p.deck, p.jib_in
FROM dbo.report AS p
) AS j
PIVOT
(
SUM(jib_in) FOR deck IN ('
+ STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
+ ')
) AS p;';
PRINT @sql;
EXEC sp_executesql @sql;
I NEED ALL THE COLUMNS TO BE PIVOTED AND SHOW ON THE PIVOTED TABLE. ANY HELP WOULD BE APPRECIATED. I AM VERY NEW AT DYNAMIC PIVOT. I TRIED SO MANY WAYS TO ADD OTHER COLUMNS BUT NO AVAIL!!
i know there are other ways please feel free to mention if there is any other way to get this right.
January 24, 2014 at 8:40 am
I would suggest that you take a look at dynamic cross tabs. Seems easier to work, especially when several columns and aggregates are involved.
http://www.sqlservercentral.com/articles/Crosstab/65048/
Here are 2 examples based on what I understood.
create table #Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,
creation int
)
insert into #Report values
('A_1',0.345,0,0,1.23,20140212),
('B_2',0.456,0,4,2.34,20140215),
('C_3',0.554,0,6,0.45,20140217),
('D_4',0.231,0,8,7.98,20140222),
('E_5',0.453,0,0,5.67,20140219),
('F_6',0.344,0,3,7.23,20140223);
DECLARE @sql NVARCHAR(MAX);
WITH Decks AS(
SELECT DISTINCT deck
FROM #Report
)
SELECT @sql =STUFF((SELECT ',MAX( CASE WHEN deck = ''' + deck + ''' THEN Jib_in END) AS ' + deck + '_Jib_in' + CHAR(13) +
',MAX( CASE WHEN deck = ''' + deck + ''' THEN rev END) AS ' + deck + '_rev' + CHAR(13) +
',MAX( CASE WHEN deck = ''' + deck + ''' THEN rev_insight END) AS ' + deck + '_rev_insight' + CHAR(13) +
',MAX( CASE WHEN deck = ''' + deck + ''' THEN jib_out END) AS ' + deck + '_jib_out' + CHAR(13) +
',MAX( CASE WHEN deck = ''' + deck + ''' THEN creation END) AS ' + deck + '_creation' + CHAR(13)
FROM Decks
FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)'),1,1,'SELECT ') + 'FROM #Report';
PRINT @sql;
EXEC sp_executesql @sql;
WITH Decks AS(
SELECT DISTINCT deck
FROM #Report
)
SELECT @sql =STUFF((SELECT ',MAX( CASE WHEN deck = ''' + deck + ''' THEN Value END) AS ' + deck + CHAR(13)
FROM Decks
FOR XML PATH(''),TYPE).value('.', 'varchar(MAX)'),1,1,'SELECT Name, ') + 'FROM #Report
CROSS APPLY(VALUES(1, ''Jib_in'', Jib_in),
(2, ''rev'', rev),
(3, ''rev_insight'', rev_insight),
(4, ''jib_out'', jib_out),
(5, ''creation'', creation))x(RowOrder, Name, Value)
GROUP BY Name, RowOrder
ORDER BY RowOrder';
PRINT @sql;
EXEC sp_executesql @sql;
DROP TABLE #report
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply