Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 DYNAMICS Pivot Function Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, August 14, 2014 3:35 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 22, 2014 3:07 PM 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;`
Post #1603180
 Posted Thursday, August 14, 2014 4:08 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 22, 2014 3:07 PM 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,
Post #1603199
 Posted Thursday, August 14, 2014 3:43 PM
 SSC Veteran Group: General Forum Members Last Login: Wednesday, December 7, 2016 7:22 PM Points: 245, Visits: 672
 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.
Post #1603492
 Posted Saturday, August 16, 2014 11:53 AM
 Forum Newbie Group: General Forum Members Last Login: Monday, September 22, 2014 3:07 PM 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 ?
Post #1604124
 Posted Saturday, August 16, 2014 3:21 PM
 SSC-Forever Group: General Forum Members Last Login: Yesterday @ 2:41 PM Points: 42,081, Visits: 39,473
 Yep. Use the ACE drivers and OPENROWSET. --Jeff Moden"RBAR 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." Helpful Links:How to post code problemsHow to post performance problems
Post #1604133
 Posted Tuesday, August 19, 2014 1:11 AM This worked for the OP
 Forum Newbie Group: General Forum Members Last Login: Monday, September 22, 2014 3:07 PM 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
Post #1604767

 Permissions