Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DYNAMICS Pivot Function Expand / Collapse
Author
Message
Posted Thursday, August 14, 2014 3:35 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:29 PM
Points: 6, Visits: 26
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 j
PIVOT
(
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:29 PM
Points: 6, Visits: 26
To add up, here are the Create and Insert statements, to get data sample for the query above

Create the following tables
CREATE TABLE [dbo].[AAG00400](
[aaTrxDimID] [int] NULL,
[aaTrxDim] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO



CREATE 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 Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Friday, September 12, 2014 7:44 PM
Points: 90, Visits: 300
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

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:29 PM
Points: 6, Visits: 26
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-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 1:47 PM
Points: 35,215, Visits: 31,667
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1604133
Posted Tuesday, August 19, 2014 1:11 AM This worked for the OP Answer marked as solution
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 4:29 PM
Points: 6, Visits: 26
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
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse