SQL to Excel : Using a SQL Table with Report Columns

Question

SQL to Excel : Using a SQL Table with Report Columns

Charles_P

Old Hand

Points: 352
More actions
January 9, 2018 at 10:01 am

#325384

This is a SQL to Excel question. We have a new SQL table with report columns and the position that they are used, that are related to columns used in Excel report. For instance if using Excel report T1, then all the column names in the SQL table that has T1 will create the column headings in Excel... Sql table position 1 is Rate column so Rate is the first column heading in Excel and so on. If using report T2, then different column names are used for column headings in Excel. My questions is how to code for this in SQL stored procedure using this table-driven column table for Excel reports? The data is from SQL and the output is Excel.
Thanks
Charles
January 10, 2018 at 3:02 am

This was removed by the editor as SPAM
January 10, 2018 at 6:15 am

This was removed by the editor as SPAM
January 10, 2018 at 8:42 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 1

subramaniam.chandrasekar - Wednesday, January 10, 2018 3:02 AM
Charles_P - Tuesday, January 9, 2018 10:01 AM
This is a SQL to Excel question. We have a new SQL table with report columns and the position that they are used, that are related to columns used in Excel report. For instance if using Excel report T1, then all the column names in the SQL table that has T1 will create the column headings in Excel... Sql table position 1 is Rate column so Rate is the first column heading in Excel and so on. If using report T2, then different column names are used for column headings in Excel. My questions is how to code for this in SQL stored procedure using this table-driven column table for Excel reports? The data is from SQL and the output is Excel.
Thanks
Charles
Please use SSIS for your requirement. If you're interested then let me know to guide you...

Heh.... it's easy to get to the moon. All you need to do is build a rocket ship. 😉

If you know how to use SSIS to create a new spreadsheet, why not just cough that bit of information up?

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 2

subramaniam.chandrasekar - Wednesday, January 10, 2018 6:15 AM
Jeff Moden - Wednesday, January 10, 2018 5:41 AM
subramaniam.chandrasekar - Wednesday, January 10, 2018 3:02 AM
Charles_P - Tuesday, January 9, 2018 10:01 AM
This is a SQL to Excel question. We have a new SQL table with report columns and the position that they are used, that are related to columns used in Excel report. For instance if using Excel report T1, then all the column names in the SQL table that has T1 will create the column headings in Excel... Sql table position 1 is Rate column so Rate is the first column heading in Excel and so on. If using report T2, then different column names are used for column headings in Excel. My questions is how to code for this in SQL stored procedure using this table-driven column table for Excel reports? The data is from SQL and the output is Excel.
Thanks
Charles
Please use SSIS for your requirement. If you're interested then let me know to guide you...
Heh.... it's easy to get to the moon. All you need to do is build a rocket ship. 😉
If you know how to use SSIS to create a new spreadsheet, why not just cough that bit of information up?
Its quite possible Jeff,
Drag an ole db source and create an ole db connection to SQL table as source.
Do check for data conversion b/w SQL & excel.
Use fuzzy lookup for data cleansing
Use derived column for adding / updating an existing table field value
Finally connect to destination ( Flat file ), Can be an excel destination and proceed to the output.
Also please be clear with your requirements, I'm unable to understand some of your typing there... @@@Charles_P

Since this is an output from SQL to Excel, hopefully there won't be a need for data cleansing.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 3

subramaniam.chandrasekar - Wednesday, January 10, 2018 8:42 AM
Sure, Jeff. We can customize as per our need but before that we need to have a clear understanding on our requirements !

Totally agree on that...

Charles_P - Tuesday, January 9, 2018 10:01 AM
This is a SQL to Excel question. We have a new SQL table with report columns and the position that they are used, that are related to columns used in Excel report. For instance if using Excel report T1, then all the column names in the SQL table that has T1 will create the column headings in Excel... Sql table position 1 is Rate column so Rate is the first column heading in Excel and so on. If using report T2, then different column names are used for column headings in Excel. My questions is how to code for this in SQL stored procedure using this table-driven column table for Excel reports? The data is from SQL and the output is Excel.
Thanks
Charles

Charles,

How do you identify the columns from the table that you want to use for the different reports? Is there something in the column name that says "T1" or "T2"? If not, what is it that identifies the columns and order of columns for each Excel "report"? Once we know that, this could be easy no matter which method someone may chose.

On the flip side of the coin, perhaps it would be better do design a "refreshable spreadsheet" that was setup to read from a given VIEW or Inline Table Valued Function in the database rather than trying to create spreadsheets on the fly.

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004313 More actions · Answer 4

subramaniam.chandrasekar - Wednesday, January 10, 2018 8:42 AM
Jeff Moden - Wednesday, January 10, 2018 7:54 AM
subramaniam.chandrasekar - Wednesday, January 10, 2018 6:15 AM
Jeff Moden - Wednesday, January 10, 2018 5:41 AM
subramaniam.chandrasekar - Wednesday, January 10, 2018 3:02 AM
Charles_P - Tuesday, January 9, 2018 10:01 AM
This is a SQL to Excel question. We have a new SQL table with report columns and the position that they are used, that are related to columns used in Excel report. For instance if using Excel report T1, then all the column names in the SQL table that has T1 will create the column headings in Excel... Sql table position 1 is Rate column so Rate is the first column heading in Excel and so on. If using report T2, then different column names are used for column headings in Excel. My questions is how to code for this in SQL stored procedure using this table-driven column table for Excel reports? The data is from SQL and the output is Excel.
Thanks
Charles
Please use SSIS for your requirement. If you're interested then let me know to guide you...
Heh.... it's easy to get to the moon. All you need to do is build a rocket ship. 😉
If you know how to use SSIS to create a new spreadsheet, why not just cough that bit of information up?
Its quite possible Jeff,
Drag an ole db source and create an ole db connection to SQL table as source.
Do check for data conversion b/w SQL & excel.
Use fuzzy lookup for data cleansing
Use derived column for adding / updating an existing table field value
Finally connect to destination ( Flat file ), Can be an excel destination and proceed to the output.
Also please be clear with your requirements, I'm unable to understand some of your typing there... @@@Charles_P
Since this is an output from SQL to Excel, hopefully there won't be a need for data cleansing.
Sure, Jeff. We can customize as per our need but before that we need to have a clear understanding on our requirements !

Absolutely agreed... which is why I questioned your comment about "Please use SSIS for your requirement". 😉 You have no explicit requirements (but we do have a good generality) and no knowledge if the OP even has SSIS available but didn't hesitate to make such a recommendation. 😉

--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.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Charles_P Old Hand Points: 352 More actions · Answer 5

I like the refreshable spreadsheet and it's what the business wants instead of say 10 excel spreadsheets we have one that refreshes with the new column names for the report. The idea is that the column names that are in rows will become column names dynamically on the excel spreadsheet. The first report key =1 but if we use another key, then those columns will appear on the Excel spreadsheet.

Attached are scripts of the tables and a template mockup.

Thanks for your help,
Charles P.

Charles_P Old Hand Points: 352 More actions · Answer 6

I managed to get the pivot to work for the columns, but the order by is not working that places the columns in the correct order. Next I need to add the data to the pivot columns. Here's the pivot code:

DROP??TABLE??##dataquery????

DECLARE @DynamicColumns as varchar(max)

Declare @TableID as Int

SET??@TableID=1 --??Set??Table??report ID??

CREATE TABLE ##dataquery

(

id INT NOT NULL,

tablename VARCHAR(50) NOT NULL,

fields VARCHAR(50) NOT NULL,

fieldvalue VARCHAR(50) NOT NULL

);

SELECT @dynamicColumns = COALESCE(@DynamicColumns + ', ', '')

+ Quotename(Header)

FROM (SELECT distinct Header

From tblRefReportTemplateLayout as L

Where L.[ReportTemplateKey] = @TableID) as fieldlist

--Order by L.[HeaderOrder]

DECLARE @FinalTableStruct as nvarchar(max)

SET @FinalTableStruct = 'SELECT ' + @dynamicColumns +

' from ##DataQuery x pivot ( max

( fields) for fields in (' + @dynamicColumns + ') ) p'

Execute(@FinalTableStruct)