How To Change Column Names Dynamically In UNPIVOT TASK

Apr 10, 2008

Dear All,

We are using UnPivot task to convert the columns into rows using the Excel File as source. But the Excel file column names are changing frequenly sometimes its having only 4 columns sometimes its 10 columns.

Everytime we are checking and unchecking the column list in Unpivot Task.

can anybody help us to solve this issue that Unpivot task should take the column name dynamically.

Thanks,
Syed

View 1 Replies


ADVERTISEMENT

Writting Column Names Dynamically

Feb 28, 2008

Can someone please help I'm writting the following query.
SELECT
(SELECT c.column_name FROM information_schema.tables T
JOIN information_schema.columns C
ON t.table_name = c.table_name
WHERE t.table_type = 'base table' and t.table_name like 'L_%' )
INTO #TempTable FROM TableA A LEFT JOIN [Server-Name].DB_Name.dbo.TableB B ON A.ID = B.ID

I'm trying to put commas between column names. How do I go about doing that?

View 16 Replies View Related

Reporting Services :: Change Column Size Dynamically Depends On Content Of The Column?

Jun 4, 2015

How to change column size dynamically depends on content of the column.

View 2 Replies View Related

Transact SQL :: Renaming Pivot Table Column Names Dynamically

Oct 30, 2015

I got a table which gets populated by stor proc where we pivot the Sum(Balance of mortgage) by YYYYMM for the whole duration of the loan term.

I have a requirement to rename the column header where the previous month end balance period be renamed to P0.

if we run the report today, then the balance as at 31/09 should show under column P0 which now shows under 201509 and then P0 keeps shifting with each month run.

How do I dynamically rename the column headers.

View 7 Replies View Related

Change Column Name Dynamically

Nov 30, 2007



Hi All,
I have a series of tables need to import to server. When creating the target tables, I want to change the columns name as well, for example:
Source table column Target table column


Name FN_Name
Age FN_Age

The problem is I suppose I don't know the columns name in source table, I want to the tasks scan the source table and make the change programmlly.

Which tasks or approaches can be used to implement this?

Thanks
Micror

View 6 Replies View Related

Change Column Names

Jan 31, 2006

i am trying to change column names in a bunch of tables.

why is this not right?

is there any sp that i can use as i have to change this in a lot of tables across two databases?

Alter Table Answers

Change Product NewProduct varchar(35)

View 8 Replies View Related

Change Chart Type Dynamically From Column To Line Graph??

May 1, 2006

I am developing several charts with column type and sub type as stacked. There is a requirement from the users that they want an option to choose the type of chart.

Is it possible to change chart type dynamically from say Column type to Line type based on user option in front-end?

Any help will be appreciated.

Thanks in advance !!

View 5 Replies View Related

How To Change Column Names In The Grid View Programatically?

Aug 31, 2007

I have consistent column names that load into a grid view. I now need to change the names in the grid view programatically. I was originally trying to get cute with SQL to do this, but I've been told my below solution will not work and I'm better off to do this in the presentation layer.  If this is true, remember I'm still wet behind the ears here...how do I do this in vs2005?
Here's my post to the SQL devs to give you an idea what I'm trying to do.
I have a query that grabs fields from a denormalized table. The result is column names Week1, Week2, Week3....Week26.  Users want to see the actual date instead of Week#. So I have a table (lkpdatecaptions ) that contains the fields "fldfieldno" and "Fldcaption". fldfieldno    Fldcaption-----------  --------------11             9/07/200712             9/14/200713            9/21/2007So fieldno 11 represent week1 and so on. So my hope is to update the alias with a query like: Select fldcaption from forecast.tlkpdatecaptionswhere fldfieldno = 11That quey returns the value of 9/7/2007 and is the value I need to represent the coumn alias name.My current query looks like this:SELECT  SUM(forecast.tblforecastdenormalized.fldwk01) AS WEEK1,So can I do something like the following?SELECT  SUM(forecast.tblforecastdenormalized.fldwk01) AS (Select forecast.tlkpdatecaptions.fldcaption from forecast.tlkpdatecaptions where fldfieldno = 11), ...

View 6 Replies View Related

SQL Question: Change Column Names Of Temp Table In SP

Apr 21, 2004

Hi,
I have a SP which returns a select query on a temp table so I get to choose column names
when I create the #table.

Can I determine column names myself based on the results of another query (in the SP)
before, or (preferably) after I create the #table and populate it.

My query is used to bind to a datagrid so I could use....

dataGrid.Columns[index].HeaderText and set it to a particular output parameter, but I want
to keep the code in the SQL.

Cheers

View 4 Replies View Related

Dynamically Pass Table Names And File Names To IS Package

Mar 1, 2015

I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.

The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.

View 1 Replies View Related

Integration Services :: SSIS Add / Remove Columns Dynamically Into Unpivot Object

Jul 9, 2015

I've created a SSIS package which takes a matrix from Excel file and insert into SQL table. It works perfectly! However, if I would add a new column into that matrix in Excel. Unpivot tool should take into process dynamically. Is there a way to provide this automatically? 

View 4 Replies View Related

Unpivot Or Custom Script Task?

Feb 18, 2008

Hi there,

I have an issue that feels like it requires an Unpivot task, but I'm struggling to visualise the best way to do it. I'd be grateful for any advice on this.


I have this dataset in my pipeline











moduleid
startdate
modenddate




Revenue
MonthsOnModule

800091
05/09/2007
30/12/2007
150
3

800094
05/09/2007
30/11/2007
148
2

800095
05/09/2007
30/12/2007
300
3


Basically, each module has a start date and an end date. Therefore, with a simple datediff, we can get to MonthsOnModule.

Each module attracts an amount of revenue, which need to be allocated equally to each month over which the module runs.

In the example above, Module 800091 lasts 3 months, generates £150 revenue, so in September 2007, it attracts £50, in October 2007 It attracts £50, In November 2007 it attracts £50.

I'm using this package to populate a fact table in an OLAP Data Warehouse. The destination table needs to hold the data like this:










moduleid
Month
value

800091
200709
50

800091
200710
50

800091
200711
50

800094
200709
74

800094
200710
74

800095
200709
100

800095
200710
100

800095
200711
100


Now that looks like a pretty straight unpivot, but in order to unpivot, you have to have columns to unpivot into rows. In this case, I would need one column for each month that the module lasted. Modules can last any number of months. Some last 40 months, and there is no reason why one couldn't last 100 months or more.

I just can't really seem to visualise my approach to this transform. Anyone got any ideas?

Thank you


View 3 Replies View Related

Integration Services :: Unpivot Task For Multiple Columns

Mar 31, 2009

I have a situation where i need to unpivot multiple columns  using ssis. The data looks like

Name  Age  products1 products2  orders1 orders2
abc      23    cycle        radio          12         24
as
Name  Age  Products   orders
abc     23      cycle       12
abc    23       radio       24

Is it possible to do this using the unpivot task in ssisMy actual data is  has 18 columns which needed to be unpivoted into one and another 18 into another one.when using unpivot task it gives an error saying only one pivotvalue key is allowed.

View 6 Replies View Related

How To Set The Dynamic Column Name In Unpivot SQL

Apr 10, 2008

Dear All,

We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:

declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

The above query while executing geting error message "Incorrect syntax near '@aw'"

please can anybody solve this issue.

Thanks,
Syed

View 6 Replies View Related

How To Set The Dynamic Column Name In Unpivot SQL

Apr 10, 2008

Hi All,
We are trying to unpivot the columns into rows but the colunms are changing dynamically, want to know how to set the dynamic value or variable in unpivot query, query is mentioned below:

declare @aw Varchar(100)
set @aw = '1990,1991'
SELECT [name], [year], [data]
FROM (SELECT * FROM t1 where [name] != 'name') p
UNPIVOT
([data] FOR [year] IN
(@aw)
)AS unpvt

The above query while executing geting error message "Incorrect syntax near '@aw'"

please can anybody solve this issue.

Thanks,
Syed

View 4 Replies View Related

Can You Unpivot On Multiple Keys With The Unpivot Transform

Oct 17, 2006

Hi all,

I have more than 1 set of columns I'm trying to normalize, can an unpivot transform handle 2 unpivots around DIFFERENT keys in the same transform?

If not, would the suggestion be unpivot into temp tables for whatever needs normalizing, than run an update query to generate the row that is desired?

Thanks in advance.


Mark

View 2 Replies View Related

Best Approach To Sending Field Names Dynamically

Mar 9, 2007

Hi,I have a C# web app that searches my database table using thefollowing search parametersSearch string, criteria (< =) and the field you want to perform yoursearch on. My understanding is that stored procedure is the way to go.What's the best way of doing this using stored procedures. Can Idefine a placeholder for the field name?Ex.SELECT field1, field2... FROM Table WHERE field1='value1' where field1and value1 are both sent from code.If it's not possible then what is the best way to approach thisproblem? I see so many searches like that on the internet. I can onlydo them with inline SQL and not stored procedure.Thank youMaz.

View 1 Replies View Related

Dynamically Creating Temp Table Names

Jul 20, 2005

Hello,I am interested in dynamically creating temp tables using avariable in MS SQL Server 2000.For example:DECLARE @l_personsUID intselect @l_personsUID = 9842create table ##Test1table /*then the @l_personsUID */(resultset1 int)The key to the problem is that I want to use the variable@l_personsUID to name then temp table. The name of the temp tableshould be ##Test1table9842 not ##Test1table.Thanks for you help.Billy

View 5 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

Hi

I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.

Thanks.

View 5 Replies View Related

T-SQL (SS2K8) :: Unpivot Sorting Column In Alphabetical Order Automatically

Jan 27, 2015

I am having problem with the unpivot function of sql 2012, i unpivot my column then i get the result that i wanted but the error that i was encountering was the unpivot is automatically sort the column in alphabetically order which is not I desire,

Here is my code

@syear nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

[Code] ....

View 1 Replies View Related

Transact SQL :: COLUMN NAME Conflicts With Type Of Other Columns Specified In UNPIVOT List

Jul 31, 2015

I am trying to convert all columns to rows in sql, but giving an error.

SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;

Error: The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.

View 6 Replies View Related

Integration Services :: Load Excel File Dynamically With Different Columns And Worksheet Names

Apr 2, 2014

 I have a situation where I want to load the Excel file dynamically, and the excel file have different columns or even worksheet name. How I could approach this? I believe there's no way to modify the meta data (specifically the mapping) in the data flow.

View 6 Replies View Related

Dynamically Change FilterExpression

Oct 1, 2007

I am trying to filter some results using the FilterExpression Property of the SqlDataSource. I have multiple drop down lists with different filtering options. I am trying to change the filter that is applied to a gridview.
Something like this example... http://blogs.vbcity.com/mcintyre/archive/2006/08/17.aspx
 Here is some of my code..Private Sub ApplyFilter()
Dim _filterExpression As String = ""
    If (Not DropDownList1.SelectedIndex = 0) And (DropDownList2.SelectedIndex = 0) And (DropDownList3.SelectedIndex = 0) Then
        _filterExpression = "CategoryName = '{0}'"
    End If    Me.SqlDataSource1.FilterExpression = _filterExpression
End Sub
Protected Sub DropDownList1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DropDownList1.SelectedIndexChanged
    ApplyFilter()
End Sub
But this doesn't seem to work. The FilterExpression doesn't keep the value. I am looking for a way to dynamically change the FilterExpression. Any Ideas or Suggestions?? Thanks.

View 5 Replies View Related

How To Dynamically Change Width?

Feb 7, 2006


Folks,

We have some reports that have optional columns. We have them working very nicely, with the column showing or hiding based on values in the report -- works great.

Except -- when the columns are present, the report spans onto two pages, when exported to PDF, in width. That's understandable, as there's a lot of extra data, and exactly what we want. However, when the columns are not present, we get empty pages instead, because the report doesn't automatically contract back onto the size that fits on one page.

Changing the report to a Matrix won't work, as the hidden columns on some of these come as sets of three, where each column in the three has different formatting (different widths, format codes, etc).

Thanks!
--randy

View 14 Replies View Related

Dynamically Change The Servername

May 10, 2006

Hi all,

I've created 1 solution and added all my packages in different projects (like DIMENSIONS, SOURCES_SAP, ...).

For each project I have a Data Source that connects to the server. The problem is that when I want to deploy a package to the server that I always need to change the Data Source before deployment.

Before SQL Server 2005 we used a connection file (which was located as well on the server as on the development pc's in the same locations) within our DTS packages. This way we didn't had to change the connections when deploying to the server.

My intention was to use the current configuration from the configuration manager(development / production) to select the servername. Unfortunately, I didn't succeed to retrieve it's value from a variable script.

I need to have a solution that dynamically changes the datasources for multiple packages depending on a specific action.

How can I achieve this the easiest way ?

Thanks in advance !

Geert

View 1 Replies View Related

Change The WHERE Conditions Dynamically

Sep 23, 2006

Is there a way I can build a case statment or similar to handle different where conditions?

I know I can do it dynamic sql but it would be nice to have a method where I can create the querries directly in a normal statement

Someting like:

Select c1, c2, c3
From Table
WHERE Case @Condition
WHEN '>' @SelColumn > @Limit
WHEN '=' @SelColumn = @Limit
WHEN '<' @SelColumn < @Limit
END

I know this doesn't work so an example that do work would be nice.

View 8 Replies View Related

How To Dynamically Change Connection?

Oct 26, 2007

Hi. I have this kind of problem since I am not very conversant with SSIS
and stripting in VB.NET.

The set-up is the following:
Flat File Source -> Script Component -> Flat File Destination

The flat file source looks like this:

NameOfFile
Headers
Data
Data
Data and many more rows of Data
NameOfAnotherFile
Headers
Data
Data
Data and many more rows
NameOfAnotherFile
Headers
Data
Data and so on in the same manner...

My stript looks like that (not very complicated):
Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)
Dim strRow As String = Row.Column0.Trim()
Dim strFolder As String = "Data"
Dim strConn As String
Dim charSep() As Char = {CChar(" ")}

If Mid(strRow.ToLower(), 1, 2) = "d0" Then


' This is the file name, so start a new connection to a new file
' which definitely does not exist yet
strConn = strFolder + strRow + ".csv"

' DestinationFile is the name of the
' Flat File Connection Manager
With Me.Connections.DestinationFile
.ConnectionString = strConn
End With

Else
Row.Column0 = String.Join(",", strRow.Split(charSep, StringSplitOptions.RemoveEmptyEntries))
End If
End Sub

I have several questions regarding this one.

1. Is it ok to change the ConnectionString property of the manager
to redirect the row to a different file? If not, what more to do?

2. If the new destination file does not yet exist, will it be created for me or will
I get an error? If I do, what to do not to?

3. The most important: What steps to take so that a row is discarded
from the pipeline in the script? I want some rows not to be directed
to the file destination. These are the lines that contain the name
of the file into which the data below belongs.

If you can optimize some steps in the script, then please do so by all means.
Thank you for any comments and the knowledge you kindly agree to share with me.
Darek

View 1 Replies View Related

How Do I Dynamically Change The TOP X Portion Of A SELECT

Oct 26, 2006

I'm sure I'm missing something. I am returning the TOP X number of customers by revenue and I'd like to change the number of records returned by passing a parameter but I keep getting an error.    @TopX int ( or varchar)    SELECT @TopX  CompanyName, Amount FROM Sales Where..... Why will this not work?

View 4 Replies View Related

Dynamically Change The SelectCommand Property

Nov 8, 2007

Hello
I have a gridview that I use on a products page, my problems is certain products have different attributes that I would like to display.
Therefore what I would like to do is change the SelectCommand property to my SQLDatasource depending on the querystring that is passed.
For instance in my page load event I would have a CASE statement with numerous SQLString Variables.
Here is the current coding for my datasource
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand="SELECT PS.ProductSizeMM AS [Coupling Size], PS.ProductWallThickness AS [To Suit], PS.Cost AS [Price], PS.Sold_By AS [Sold by] FROM tblProduct AS P INNER JOIN tblProductSize AS PS ON P.ProductCode = PS.ProductCode WHERE (P.ProductDescription = @ProductDescription) ORDER BY PS.Sorter">
<SelectParameters>
     <asp:QueryStringParameter Name="ProductDescription" QueryStringField="ProductDescription" />
</SelectParameters>
</asp:SqlDataSource>I have tried declaring a string variable in my page load event (SQLString) then setting the
SelectCommand="SQLString" but this causes a syntax error
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near 'SQLString'.
Any help would be greatly appreicated!!

View 3 Replies View Related

Dynamically Change Based On Date

Feb 26, 2014

I have to change the name of the history table dynamically to what is passed in the start date.

If @start_date= '1/1/2014' then it should be history_jan14
If @start_date ='02/01/2014' then it should be history_feb14 and so on.

Is there a way you can do it ?I am using SQL Server 2008.

Code :

DECLARE @start_date datetime
DECLARE @end_date datetime
SET @start_date = '01/01/2014'
SET @end_date = '02/01/2014'

[code]...

View 2 Replies View Related

Howto Dynamically Change The BulkInsertTableName

Mar 19, 2007

Hi,

I'm novice to SSIS and looking for some help on SSIS dtexec (SQL Server 2005).

Is it possible to change the BulkInsertTableName when running a package via dtexec /SET?

My test scenario contains:

- SQLServer 2005 SP2, servername: SDPM01, instancename: GWLINST1, databasename 1: TEST, databasename 2: DEV, tablename 1: Test_Table1 (both in TEST and DEV database), tablename 2: Test_Table2 (both in TEST and DEV database)

- 1 Data Flow task in BIDS (SSIS)

- 1 Data Flow Source: Flat File Source (Flat File Connection Manager name: FTP File Output + CSV file with a few lines of data that needs to be inserted in a SQL Server table)

- 1 Data Flow Destination: SQL Server Destination (OLE DB Connection Manager name: SDPM01GWLINST1.TEST

I can dynamically change the name of the database via:

DTExec /F "Package.dtsx" /SET "Package.Connections[SDPM01GWLINST1.TEST].InitialCatalog;DEV"

How can I also dynamically change the table name where the data from the CSV file will be inserted?

DTExec /F "Package.dtsx" /SET ...

Thanks in advance,

Geert

View 8 Replies View Related

How To Change Dynamically The Connection String.

Nov 15, 2007

Hi,

I am looking to dynamically change the connection string in my SSIS package, to avoid changing the connection string each time I want to run in different environments.

Thanks in advance

View 3 Replies View Related

Change The Chart Size Dynamically

Aug 10, 2005

Hi All,

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved