Is there an equivalent to SQL Server's Pivot clause? If not, how could I do a Pivot from within an ADS query?

asked 15 Oct '14, 12:57

gverge393's gravatar image

gverge393
126111118
accept rate: 100%


There's nothing built into ADS, but you could write a script that creates and populates a temp table based on your needs.

link

answered 15 Oct '14, 14:01

Joachim%20Duerr's gravatar image

Joachim Duerr
8.7k1031133
accept rate: 18%

This might be helpful:

CREATE PROCEDURE GeneratePivot
   ( 
      SelectS VARCHAR ( 1000 ),
      PivotCol VARCHAR ( 100 ),
      Summaries VARCHAR ( 100 ),
      GroupBy VARCHAR ( 100 ),
      OtherCols VARCHAR ( 100 ),
      ResultTable VARCHAR ( 25 )
   ) 
BEGIN 
Declare @Vals VarChar(65000), @Crs1 Cursor, @OtherCols VarChar(100);

Set @OtherCols=_OtherCols;
If @OtherCols<>'' Then
Set @OtherCols = ', ' + @OtherCols;
End If;

Create Table #Temp1Pivot(Pivot varchar(100));

Execute Immediate 'Insert into #Temp1Pivot Select ' + _PivotCol + ' From (' + _SelectS + ') A Group By 1;';

Set @Vals='';
Open @Crs1 AS Select Pivot From #Temp1Pivot; 
While Fetch @Crs1 Do
Set @Vals=@Vals+', '+Replace(Replace(_Summaries, '(', '(CASE WHEN ' + _PivotCol + '=''' +
  Trim(@Crs1.Pivot) + ''' THEN '), ')[', ' END) as [' + Trim(@Crs1.Pivot));
End While;
Close @Crs1;

Execute Immediate 'Select ' + _GroupBy + @OtherCols + @Vals + ' Into ' + _ResultTable + ' From (' + _SelectS + ') A Group By ' + _GroupBy;
//Source: http://weblogs.sqlteam.com/jeffs/archive/2005/05/02/4842.aspx
END;
link

answered 19 Oct '14, 14:45

Jack's gravatar image

Jack
545333654
accept rate: 14%

Your answer to the original question.
If responding to a request for additional information, please edit the question or use the comment functionality.
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "Title")
  • image?![alt text](/path/img.jpg "Title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×325
×172
×24

Asked: 15 Oct '14, 12:57

Seen: 2,281 times

Last updated: 19 Oct '14, 14:45

Advantage Developer Zone Contact Us Privacy Policy Copyright Info


Powered by Advantage Database Server and OSQA
Disclaimer: Opinions expressed here are those of the poster and do not necessarily reflect the views of the company.