## DYNAMICS Pivot Function

 Author Message mahmoodsaadi Forum Newbie Group: General Forum Members Points: 6 Visits: 28 How can I get the data retrieved from the exec function below into Excel `DECLARE @columns NVARCHAR(MAX) , @columns_n NVARCHAR(MAX) , @sql NVARCHAR(MAX);SET @columns = N'';SET @columns_n = N'';SELECT @columns += N', X.' + QUOTENAME(aaTrxDim)FROM ( SELECT RTRIM(aaTrxDim) AS aaTrxDim FROM dbo.AAG00400 ) AS Y;SELECT @columns_n += N', ISNULL(' + N'X.' + QUOTENAME(aaTrxDim) + N', '''' ) AS' + QUOTENAME(aaTrxDim)FROM ( SELECT RTRIM(aaTrxDim) AS aaTrxDim FROM dbo.AAG00400 ) AS Y;SET @sql = N'SELECT ' + STUFF(@columns_n, 1, 2, '') + 'FROM(SELECT ROW_NUMBER() over(partition by A.aaTRXDIM order by B.aaTrxDimCode) AS DimensionCodeRank, A.aaTrxDim,B.aaTrxDimCode FROM AAG00400 AS A LEFT OUTER JOIN AAG00401 AS B ON A.aaTrxDimID = b.aaTrxDimID ) AS jPIVOT( MAX(aaTRXDIMCode) FOR aaTRXDIM IN (' + STUFF(REPLACE(@columns, ', X.[', ',['), 1, 1, '') + ')) AS X;';PRINT @sql;EXEC sp_executesql @sql;` mahmoodsaadi Forum Newbie Group: General Forum Members Points: 6 Visits: 28 To add up, here are the Create and Insert statements, to get data sample for the query aboveCreate the following tables`CREATE TABLE [dbo].[AAG00400]( [aaTrxDimID] [int] NULL, [aaTrxDim] [nvarchar](max) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GOCREATE TABLE [dbo].[AAG00401]( [aaTrxDimID] [int] NULL, [aaTrxDimCode] [nvarchar](max) NULL) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]GO`Then, insert the following values `INSERT INTO dbo.AAG00400 ( aaTrxDimID ,aaTrxDim )VALUES ( 1, 'Cost Center' ), (2, 'Location') , (3,'Sector') INSERT INTO AAG00401 ( aaTrxDimID, aaTrxDimCode )VALUES ( 1, 'Administration' ), ( 1, 'Sales' ), ( 1, 'Marketing' ), ( 2, 'Export' ), ( 2, 'Local' ), ( 3, 'B2B' ), ( 3, 'B2C' )`Now, you can run the query above and check the sample data.Your reply is highly appreciated, Xedni SSCarpal Tunnel Group: General Forum Members Points: 4005 Visits: 760 Copy paste works nicely.Alternatively, if you want it to be written directly to an excel document, the easiest way (after copy/paste) would be to output the results to file. You can do this by pressing Ctrl+Shift+F (note, that by default results are set to grid, or Ctrl+D. The other option is results to text which is Ctrl+T).Once you have it outputting to a file, when you execute your query it will open a save file dialog. Save it as a csv and let it rip.If you want to be able to adjust some of the settings, right click anywhere in the query window and click "Query Options", then under Results > Text, you have some options you can play with.If you need something ongoing and programmatic you'd probably want to build an SSIS package. Executive Junior Cowboy Developer, Esq. mahmoodsaadi Forum Newbie Group: General Forum Members Points: 6 Visits: 28 Thanks for the feedback, The way I have started all this dynamic code is to get it automatically into Excel. In my case, when any new dimension is added, the result will be automatically updated. Do you think the copy/paste fits in here !!I am looking for an automatic criteria to get this exec function into Excel.Any ideas ? Jeff Moden SSC Guru Group: General Forum Members Points: 507213 Visits: 44277 Yep. Use the ACE drivers and OPENROWSET. --Jeff ModenRBAR is pronounced ree-bar and is a Modenism for Row-By-Agonizing-Row.First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair Helpful Links:How to post code problemsHow to post performance problemsForum FAQs mahmoodsaadi Forum Newbie Group: General Forum Members Points: 6 Visits: 28 I have created a stored procedure for the exec function above. In Excel, I have added an existing connection with SQL type calling the stored procedure.It worked perfectly.Thanks for your help