Importing Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns

Aug 25, 2007

Hi Craig/Kamal,

I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.

Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.

We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.

I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.

Your help in this respect is highly appreciated!

Thanks,

Hi Anthony, I am glad the Web cast was helpful.

Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.

I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1

Thanks

Craig Guyer
SQL Server Reporting Services

View 12 Replies

Integration Services :: Export To Excel Dynamic Number Of Columns

Sep 11, 2015

We have a requirement to produce adhoc Excel reports with a standardized header page with a disclaimer attached. We want to be able to feed in a SQL Statement, or a table with the resultset from a SQL Statement and have SSIS populate an existing blank Excel workbook, which the disclaimer attached. The use of xp_cmdshell is not an option.I've spent a lot of time looking for solutions on the web and it seems though its not possible - although many articles are 3-5 years old. Before I throw in the towel, I just wanted to get feedback from this group if it still is not possible in the latest versions of SQLServer and SSIS, or to ask if there are any other 3rd party solutions that can do this today.

View 5 Replies View Related

Dynamic Multi-sheet Excel Destination

Feb 4, 2008

I need to take 5 or 6 select statements to excel. Here are my limitations...

- each of the queries (thankfully) have the same data format
- each of the queries could return more than 65k, so a new worksheet needs to be generated dynamically.
- the names of the excel worksheets need to be custom, but a naming scheme would have to be developed for queries that ran over into multiple worksheets.

What's the smartest way to do this?

I'm having a hard time getting my head around this. I would love any help... I know I'm not breaking any new ground here. I've found pieces of what I'm doing on lots of forums, but never the exact thing. The complexities compound quickly when dealing with dynamic excel worksheets. =)

View 3 Replies View Related

Integration Services :: Importing A File With Dynamic Columns

Jul 16, 2015

I am new to SSIS and C#. In SQL Server 2008 I am importing data from a .csv file. Now I have the columns dynamic. They can be around 22 columns (some times more or less). I created a staging table with 25 columns and import data into it. In essence each flat file that I import has different number of columns. They are all properly formatted only. My task is to import all the rows from a .csv flat file including the headers. I want to put this in a job so I can import multiple files into the table daily.

So inside a for each loop I have a data flow task within which I have a script component. I came up(research online) with the C# code below but I get error:Index was outside the bounds of the array.I tried to find the cause using MessageBox and I found it is reading the first line and the index is going outside the bounds of the array after the first line.

My File1Conn is the flat file connection instead I want to read it directly from a variable User::FileName

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.Windows.Forms;
using System.IO;

[code]....

View 8 Replies View Related

Dynamic Number Of Columns

Jun 2, 2005

when using sorred procedure to create a temporary table is it possable to base the number of columns in that table on another variable?

View 5 Replies View Related

T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot

Mar 9, 2015

I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?

Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),

[code]....

View 1 Replies View Related

SQL Server 2012 :: Read Dynamic Columns From Excel File Into SSIS

Nov 11, 2014

I have an excel file which has dynamic columns

i.e. Col1, Col2, Col3 this week. next week i will have a new Col4 in the sheet. This will keep on adding every week.

My problem is to Unpivot the data

Date 8/2/2013 8/9/2013 8/16/2013

Stock 1,561 1,661 1,761

i.e. the abobe table should become as

Date Stock

8/2/2013 1561
8/9/2013 1661
8/16/2013 1,761

How can I unpivot the dynamic columns given that the columns will keep on increasing every week.

View 1 Replies View Related

Dynamic Header Column Names In Excel

Jul 3, 2007

Folks,

I am running into an issue while trying to export data to a spreadsheet. I actually don't know how to do it... Considering I only know the column names by the time I execute my procedure, I can't use the Excel Destination to export data.

With DTSs I would create an ActiveX script to execute the procedure which loads the results into a temp table. After that I would select everything from this temp table and load the results into a record set, looping through this record set to create the destination spreadsheet with the dynamic column names.

When it comes to SSIS we are advised to write vb.net scripts instead of ActiveX... These ones do not have records set's but dataset's, which at first glance are only applicable to xml and not xls files (when I try to define a variable as a dataset in my vb.net code, I face a message saying: Missing reference required to assembly System.Xml...).

How I would create this spreadsheet using a vb.net code in SSIS packages? Please, help...

Thank you.

View 5 Replies View Related

Importing Data From Excel Sheet

Oct 19, 2007

i using the select command

select * from [excel sheet name$];

i also using open file dialoug to specify the excel book this book should be pass to the select command at run time
as a parameter

so plz help me with suitable example

View 1 Replies View Related

Integration Services :: Dynamic Column Mapping Between Excel And Fact Table

May 18, 2011

Have to create SSIS package for the below requirement:

I have source data in 2 excel files. Data from both these excel files should be loaded to the same single Fact table.

The column names in excel files and table are not same. I have a Reference table which has the column mappings between excel and Fact Table.

I have to refer this Reference Tabel for column mappings, plus i have to add some derived columns (Created_Date) to load the Fact_Table.

I have given a sample data structure below:

Source Data
Excel1_Order.xls
OrderNumber     OrderQuantity    OrderDate
Order10001             100               01-01-2011
Excel2_Customer.xls
CustomerNumber      CustomerName    CustomerAddress

[Code] ....

Is there any way to handle this in SSIS?

View 16 Replies View Related

Getting Null Values While Importing Data From Excel Sheet To Sqql Server

Aug 16, 2007

Hai Friends,
Actually i want to import excel sheet in to sql server tables. I am writing the following query                                                                                           SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Book1.xls','SELECT * FROM [Sheet1$]') and able to retireve the data but the problem is the data present in the excel sheet is

Username
password

hello
12345

12345
hello

hai-123
haihai

haihello
hello-123
Once i run this query i am getting it as
Username Password                                                                                                                                                                                           hello NULLNULL hellohai-123 haihaihaihello hello-123can anybody please help me out in this case and how to solve this problem and if you have any piece of code please pass it on to me and i am in very urgent need waiting for your replies thanks in advance.

View 2 Replies View Related

Dynamic Columns For Dynamic SQL

Mar 9, 2007

I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.

Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.

I have come to understand that a dynamic anything is a moving target!

Please advise.

View 3 Replies View Related

Dynamic File Name For Importing?

Apr 18, 2012

I have a stored procedures that will import 28 CSV files with different columns layout for each one, into their own table in SQL Server 2008 and a master stored procedure which will execute the other 28 store procedures.

All the CSV files are stored on the network drive in one folder.The naming of these files will vary from week to week and I don't want to manually change the names in the stored procedures. Each table name has a unique Identifier F1 to F28 that will be a constant before the rest of the name for example ...

F1_country23
F2_region12

Can I use the unique Identifier to add an wildcard or variable to the stored proc so it will pick any file in the folder for example with "F1" or "F2" meaning format layout 1 or 2 and use the correct stored procedure like below?

WHEN 'F1' THEN 'usp_F1_ImportScript'
WHEN 'F2' THEN 'usp_F2_ImportScript'

Example SQL scripts so far ...

Code:

Create PROC [dbo].[usp_F1_ImportScript]
-- Check if template table already exists and if it does then delete the table
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[CSVTest]') AND type in (N'U'))
DROP TABLE dbo.CSVTest
-- Create a new template table

[Code] ....

the SP which will run the 28 SP

Code:
CREATE PROCEDURE [dbo].[usp_FilePicker] (@FileName VARCHAR(255))
AS
--check if @FileName is null
IF @FileName IS NULL
BEGIN
SELECT 'No Files Match' AS ERROR

[Code] .....

I cannot use SSIS because other users from different countries (50+ so far) may need to add/delete columns in the raw text/cvs files (for their own test environment) meaning there may be changes needed to made to the SQL scripts and with documentation any user without experience with MSSQL would be able to make simple changes.

View 1 Replies View Related

Dynamic File Importing

May 23, 2008

Hello,

I have a requirement to create a reusable process that will be able to read a file and insert it into a table. The complication comes from the fact that the process will not just need to handle one file format, but files that will come in up to 20 different formats. (Some will be fixed width, some will be comma delimited, column names will be different.)

Since these formats may potentially change, such as a new column being added, the process needs to be dynamic enough to handle this without changing the package itself. While this task is somewhat daunting, I was hoping that SSIS might be able to handle it.

One thought that I had was to somehow have dynamic file connection, with the information about the file coming from a table. (I.e. The table would store values to specify the column name for a file type, the delimiter used, etc.) However, I don't know if this is something that SSIS is equipped to handle.

Any suggestions would be much appreciated.

Thanks,
Attila

View 3 Replies View Related

Importing Dynamic CSV Files

Jan 29, 2008

I need to write a genric CSV importer. one set of CSVs define the formats of all teh other CSVs. The format of the second set of CSVs is not know at the time of creating this project/SSIS package.

Imagine having two sets of CSV files.

Each file in the first set of CSVs define the format of files in the second set of CSVs
The first set always have the same format. Each row of the CSV file defines a field with a Name and Type.
The first two columns of each row of in the first set of CSVs have a FormatName and index.
So a simple file may have:

Format1,1,Name,string
Format1,2,Surname,string
Format1,3,DOB,Date
Format1,4,Email,string

The second set of CSVs contains records that have to comply with the format define in the first set.
So in this aprticular case, a CSV file in the second set may have records such as:

Format1,John,Doe,2007-01-01,john@doe.com
Format1,Jane,Doe,2007-02-02,jane@doe.com

The problem I have is creating a generic SSIS package for this.
The first task, loading the first set of CSV file is fairly simple. The CSV format is known.
But the second task is a bit trickier.

Assuming I have SQL tables to load the data.
One 'Fields' record for each row in the CSVs from the fiirst set.
One 'Rows' record for each row in the CSVs from the second set,
One 'Values' record for each value in each row in the CSVs from the second set,
Something like:

TABLE Fields (

FieldID int IDENTITY,
FormatName varchar(100),
Position int,
ColumnName varchar(100),
ColumnType varchar(20) )

TABLE Rows (

RowID int IDENTITY )

TABLE Values (

ValueID IDENTITY,
RowID int,
FieldID int,
ColumnValue varchar(100) )

What would be the best/easiest SSIS approach:

- Dynamically create a SSIS package based on the content of each CSV fiel of the first set and execute that package for each file in the second set, selecting the correct package to execute (all records within a CSV file belong to the same format).
- Write a single SSIS package that iterates through the rows of the second CSVs, does a lookup for each value of each row to find the field name and make an insert into the DB
- Other SSIS method?
- Don't use SSIS to parse the CSV and call a custom C# task?
- Don't use SSIS at all ?

Thanks

View 1 Replies View Related

SQL 2012 :: Insert Outstanding Totals Into Different Columns In Excel Sheet

Jul 21, 2014

I have a data table which has list of bills with date and outstanding total.

If the bills dates between 0 and 30 I like to insert the Outstanding total into Colum K ,
If the bills dates between 31 and 60 I like to insert the Outstanding total into Colum L ,
If the bills dates between 61 and 90 I like to insert the Outstanding total into Colum M and so on ..

I tried below formula but it is not working

CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 0 and 30 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('K')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 31 and 60 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('L')
CASE DATEDIFF(day, Ac_Billbook.BillDate, getdate()) Between 61 and 90 THEN Insert into (Ac_Billbook.OutstandingTotal) Value ('M')

View 9 Replies View Related

Integration Services :: Reading 500 Columns In Excel Sheet Using SSIS

Nov 17, 2010

I have to transform 500 columns from an excel sheet to Sql Server. In Excel 2k3 , I can read a max of 256 columns only.If I use Excel 2k7, then SSIS 2k5 excel source does not support excel 2k7. If I use ole db source then again it can read a max of 256 columns.how can we read 500 columns in excel sheet (Around 10000 rows) efficiently using SSIS 2k5.

View 12 Replies View Related

Dynamic Importing Of Files And Data

Aug 4, 2000

I am new to SQL7 and I am trying to run a package that will import fixed text files in a folder and transform the data to a table. I am using a good example found in sywink.com for dynamically importing files in a directory, but I am getting an error message when there is more than one file in the folder. Also, I am able to display all the files during the loop process, but the file initially set for the source connection is the only one that transfers data. I have the close(Transform) connection checkbox option checked, and have tried other methods of closing the connection before the new source name is called, none have helped. Does anyone have any solutions to this type of problem? Or know of other methods used for my situation?
Thanks
James

View 1 Replies View Related

SSIS - DataFlowTask - Excel Source - Dynamic Excel Template

Mar 13, 2008

Hi,

I am creating an SSIS package witha a Dataflow task, which reads from an Excel source and then uses script component to dumpt the data to multiple tables in Sql Server database

I need to some how make my Excel source dynamic, that is my excel template which i would be using to map the excel columns to script component's input columns would be dynamic..

In other words, I should be able to define the Excel Source, Column Mapping Information, Precedence constraint to the Script component dynamically

Please suggest how could i accomplish this

Regards,
Kalyan

View 8 Replies View Related

T-SQL (SS2K8) :: Converting Row Values To Columns With Dynamic Columns

Jun 11, 2015

Basically, I'm given a daily schedule on two separate rows for shift 1 and shift 2 for the same employee, I'm trying to align both shifts in one row as shown below in 'My desired results' section.

Sample Data:

;WITH SampleData ([ColumnA], [ColumnB], [ColumnC], [ColumnD]) AS
(
SELECT 5060,'04/30/2015','05:30', '08:30'
UNION ALL SELECT 5060, '04/30/2015','13:30', '15:30'
UNION ALL SELECT 5060,'05/02/2015','05:30', '08:30'
UNION ALL SELECT 5060, '05/02/2015','13:30', '15:30'

[Code] ....

The results from the above are as follows:

columnAcolumnB SampleTitle1 SampleTitle2 SampleTitle3 SampleTitle4
506004/30/201505:30 NULL NULL NULL
506004/30/201513:30 15:30 NULL NULL
506005/02/201505:30 NULL NULL NULL
506005/02/201513:30 15:30 NULL NULL

My desired results with desired headers are as follows:

PERSONSTARTDATE STARTIME1 ENDTIME1 STARTTIME2 ENDTIME2
506004/30/2015 05:30 08:30 13:30 15:30
506005/02/2015 05:30 08:30 13:30 15:30

View 3 Replies View Related

Integration Services :: Create Excel With Sheet-name And Column Name Dynamically?

Jun 27, 2015

how to create excel with sheetname and column name dynamically

View 4 Replies View Related

SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule

Nov 23, 2007

Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.

So, is this possible using data driven subscriptions? Scenario is:

1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.

Any tips on how to get this working?

Thanks

Mark Smith

View 3 Replies View Related

Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?

May 2, 2007

If anyone could confirm...

SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.

E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.

First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.

However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.

Question: I thought the filters would remain dynamic and be applied on each sync?

I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!

Question: I wonder why if parent records are supplied, why not child records?

If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...

Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?

View 4 Replies View Related

Importing Text Files With Different Number Of Columns

Oct 6, 2007

Hi!

I have a bit of a problem with importing (DTS Package) from a text file into an existing DB.

The file has no column names and every file only contain 1 row. This row varies in number of columns.

My first thought was to create a table with the maximum numbers oc columns that can appear in the text file.
An example:

Table with 4 columns; Col001, Col002, Col003 and Col004

The first text file:
123,Peter,Ocean Street,NY

The second text file:
356,John,Park Avenue

My aim is that the second file should append to the table, leaving Col004 as (NULL)

Any ideas how to get this to work?

Expekt

View 3 Replies View Related

Dynamic Columns

Mar 18, 2004

Hi all, a quicky.

What is wrong with:

CREATE PROCEDURE dbo.QuotePrice
@DateFrom datetime,
@DateTo datetime
AS
declare @days as int
declare @price as smallint
declare @daycolumn as nvarchar(6)

set @price = 0

set @days = DATEDIFF(day, @DateFrom, @DateTo)

set @daycolumn = 'Day_' + CAST(@days AS nvarchar(2))

set @price = (SELECT @daycolumn FROM pasPriceTable)

return @price
GO

Problem is:
Syntax error converting the nvarchar value 'Day_10' to a column of data type smallint

How do I set a variable to a result from a query?
Thanks,
/ j0rge

View 9 Replies View Related

Dynamic Columns

Sep 6, 2007

Hi all again,I need an output like :|----|----|----|---|------||row1|row2|row3|...|row(n)||----|----|----|---|------|is possible to create output like that, without querying manuallycause too many dynamic columns in my application.sorry if my question a bit weird cause i'm a newbie in MSSQL.Cheers.

View 1 Replies View Related

Dynamic Columns

Sep 20, 2007

My report has a large number of rows in the details section, This wastes a lot of space. I'd like to spread these rows across multiple columns in the details section of my table.

What i'm currently getting...
---ROW1----------
---ROW2----------
---ROW3----------
---ROW4----------
---ROW5----------
---ROW6----------
---ROW7----------
---ROW*----------
---ROW9----------

What I'd like to see...

---ROW1---------- ---ROW2---------- ---ROW3----------
---ROW4---------- ---ROW5---------- ---ROW6----------
---ROW7---------- ---ROW8---------- ---ROW9----------

Is this possible, if so, how??

View 4 Replies View Related

Are Dynamic Columns Possible ?

Mar 19, 2007

Hi folks

I'm trying to roll out / createcolumns dynamically, based on a parameter selection, which in turn can calculate the number of columns to be inserted.

For example, if my parameter selects the first Option viz. Business Unit, I need to find out how many standard business units exist (un-specified and depending on the client) and have that many standard width columns generated and have each populated with a column header label + appropriate value.

the columns have to be dynamic and the same information is preferred if it is NOT presented in a drill down format (row-wise).

Thanks for any inputs coming in :)

Prashanth

View 1 Replies View Related

Dynamic Excel Destination In DTS

Jan 29, 2004

Hello everybody .
I am building DTS transfer data from
SQL server into Excel file

source query constant ,but destination will be supplied by parameter

At design time I created destination
excel file and saved a copy of it like
C: empl_excel.xls

presently dts work in following order
1. set datasource of destination
from global varaibale(@@X)
2. execute xp_cmdshell to copy
C: empl_execel.xls to file in @@X
3.Run transformation

How to eliminate step 2 ?
If I run step 1 and 3 ,I get error "table does not exist"
How dynamicly create table in excel and map columns for transfer

Thank you

alex

View 3 Replies View Related

Dynamic Excel Sources

Feb 13, 2006

Please help....

I have different files that are sent from our vendors. Some are TXT and some are XLS. Some will have the same structure. I plan on grouping these together as best as I can.

My main problem is that I would like to go from one source that matches a group of files to a single SQL table. I'm still learning about SSIS and its capabilities. If I can get pointed in the right direction or have an example to work from, that would be great.

I've tried googl'ing to find some step by step, examples, and hints to do this right, but so far I'm at a bit of an impasse.

Thanks,
Dave

View 4 Replies View Related

Query With Row Number,Union And Dynamic Orderby.

Dec 19, 2007

Hi,
Can some one help me to complete this query.
My requirement is to convert the existing stored proc(given below) to one that allows dynamic paging as well as sorting(sql server 2005). I gave a try but invein. I am running out of time.

Existing procedure:

ALTER PROCEDURE [dbo].[spGetAllItems]
@condition varchar(7500),
@ModelId int =0,
@CategoryId int=0,
@SourcingType int=0,
@StartRow as int = 1,
@StopRow as int = 100000
AS
declare @erow int
declare @Cond varchar(255)

select @Cond=''

if @CategoryId<>0
select @Cond= ' and tbItemMaster.Category=' + cast(@CategoryId as varchar)
if @SourcingType<>0
select @Cond= ' and tbItemMaster.SourcingType=' + cast(@SourcingType as varchar)

if @StopRow=-1
select @StopRow=60
set @erow=@StartRow + @StopRow

exec('Declare @t_table table
(
[SpecialId] [int] IDENTITY (1, 1) Primary key NOT NULL,
[id] [int],
[category] [int] NULL ,
[model] [int] NULL ,
[mtype] [varchar] (50) NULL,
[itemclass] [varchar] (50) NULL,
[itemcode] [nvarchar] (25) NULL ,
[ItemCondition] [tinyint] NULL ,
[SourcingType] int,
[Title] [nvarchar] (50) NULL ,
[Description] [nvarchar] (4000) NULL ,
[InternalUse] [nvarchar] (4000) NULL,
[CompatibleModels] [varchar] (8000) NULL ,
[Quantity] [int] NULL ,
[MinOrder] [int] NULL ,
[Warranty] [tinyint] NULL ,
[Price1] [decimal](6, 2) NULL ,
[Price2] [decimal](6, 2) NULL ,
[Price3] [decimal](6, 2) NULL ,
[Price4] [decimal](6, 2) NULL ,
[LotSale] [bit],
[PricingRule] int ,
[Weight] [decimal](7, 2) NULL ,
[PackageSize] [int] NULL ,
[promo] [int] NULL ,
[SmallImage] [nvarchar] (255) NULL ,
[BigImage] [nvarchar] (255) NULL ,
[StartDate] [datetime] NULL ,
[EndDate] [datetime] NULL ,
[Info1] [nvarchar] (50) NULL ,
[Info2] [nvarchar] (50) NULL ,
[Info3] [nvarchar] (50) NULL ,
[Info4] [nvarchar] (50) NULL ,
[Info5] [nvarchar] (50) NULL ,
[Info6] [nvarchar] (50) NULL ,
[Info7] [nvarchar] (50) NULL ,
[Info8] [nvarchar] (50) NULL ,
[Info9] [nvarchar] (50) NULL ,
[Info10] [nvarchar] (50) NULL ,
[Info11] [nvarchar] (50) NULL ,
[Info12] [nvarchar] (50) NULL ,
[Info13] [nvarchar] (50) NULL ,
[Info14] [nvarchar] (50) NULL ,
[Info15] [nvarchar] (50) NULL ,
[Info16] [nvarchar] (50) NULL ,
[Info17] [nvarchar] (50) NULL ,
[Info18] [nvarchar] (50) NULL ,
[Info19] [nvarchar] (50) NULL ,
[Info20] [nvarchar] (50) NULL ,
[Info21] [nvarchar] (50) NULL ,
[Info22] [nvarchar] (50) NULL ,
[Info23] [nvarchar] (50) NULL ,
[Info24] [nvarchar] (50) NULL ,
[Info25] [nvarchar] (50) NULL ,
[Status] [tinyint] NULL ,
[AllowBuy] [char] (1) NULL ,
[PageName] [varchar] (200) NULL ,
[Locality] [int] NULL ,
[Location] [int] NULL ,
[CreatedBy] [int] NULL ,
[CreatedOn] [datetime] NULL ,
[UpdatedBy] [int] NULL ,
[UpdatedOn] [datetime] NULL,
[BrandId] int,
[CategoryId] int,
[ModelId] int,
[ModelName] varchar(255),
[BrandName] varchar(255),
[CategoryName] varchar(255)
);
DECLARE @EndRow int
DECLARE @reccount int

select @reccount=count(*) from (SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + '
Union
SELECT tbItemMaster.id
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + ') tbl

set @EndRow= + ' + @StartRow + '+' + @StopRow + '
--Set RowCount @EndRow;
insert @t_table
(
[id] ,
[category] ,
[model] ,
[mtype],
[itemclass],
[itemcode] ,
[ItemCondition] ,
[SourcingType],
[Title] ,
[Description] ,
[InternalUse],
[CompatibleModels],
[Quantity] ,
[MinOrder] ,
[Warranty] ,
[Price1] ,
[Price2] ,
[Price3] ,
[Price4] ,
[LotSale] ,
[PricingRule],
[Weight] ,
[PackageSize] ,
[promo] ,
[SmallImage] ,
[BigImage] ,
[StartDate] ,
[EndDate] ,
[Info1] ,
[Info2] ,
[Info3] ,
[Info4] ,
[Info5] ,
[Info6] ,
[Info7] ,
[Info8] ,
[Info9] ,
[Info10] ,
[Info11] ,
[Info12] ,
[Info13] ,
[Info14] ,
[Info15] ,
[Info16] ,
[Info17] ,
[Info18] ,
[Info19] ,
[Info20] ,
[Info21] ,
[Info22] ,
[Info23] ,
[Info24] ,
[Info25] ,
[Status] ,
[AllowBuy] ,
[PageName] ,
[Locality] ,
[Location] ,
[CreatedBy] ,
[CreatedOn] ,
[UpdatedBy] ,
[UpdatedOn] ,
[BrandId] ,
[CategoryId] ,
[ModelId],
[ModelName],
[BrandName],
[CategoryName]
)
select * from (SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id' + @Condition + ' Order By tbItemMaster.UpdatedOn desc) tbl
Union
SELECT top 100 percent tbItemMaster.id,tbItemMaster.category,tbItemMaster.model,tbItemMaster.mtype,tbItemMaster.ItemClass, tbItemMaster.itemcode, tbItemMaster.ItemCondition,tbItemMaster.SourcingType, tbItemMaster.Title, tbItemMaster.Description,tbItemMaster.InternalUse, tbItemMaster.CompatibleModels,
tbItemMaster.Quantity,tbItemMaster.MinOrder, tbItemMaster.Warranty, tbItemMaster.Price1, tbItemMaster.Price2, tbItemMaster.Price3,tbItemMaster.Price4,tbItemMaster.LotSale,tbItemMaster.PricingRule, tbItemMaster.Weight, tbItemMaster.PackageSize,
tbItemMaster.promo,tbItemMaster.SmallImage, tbItemMaster.BigImage, tbItemMaster.StartDate, tbItemMaster.EndDate, tbItemMaster.Info1, tbItemMaster.Info2, tbItemMaster.Info3, tbItemMaster.Info4,
tbItemMaster.Info5, tbItemMaster.Info6, tbItemMaster.Info7, tbItemMaster.Info8,tbItemMaster.Info9, tbItemMaster.Info10, tbItemMaster.Info11, tbItemMaster.Info12,
tbItemMaster.Info13, tbItemMaster.Info14, tbItemMaster.Info15, tbItemMaster.Info16, tbItemMaster.Info17, tbItemMaster.Info18,
tbItemMaster.Info19, tbItemMaster.Info20, tbItemMaster.Info21, tbItemMaster.Info22, tbItemMaster.Info23, tbItemMaster.Info24,
tbItemMaster.Info25,tbItemMaster.Status, tbItemMaster.AllowBuy, tbItemMaster.PageName,
tbItemMaster.Locality, tbItemMaster.Location,tbItemMaster.CreatedBy, tbItemMaster.CreatedOn, tbItemMaster.UpdatedBy,tbItemMaster.UpdatedOn,

tbBrand.id AS BrandId, tbCategory.Id AS CategoryId, tbModel.id AS ModelId,tbModel.Model as ModelName,tbBrand.Brand as BrandName,tbCategory.Category as CategoryName
FROM tbItemMaster INNER JOIN
tbModel ON tbItemMaster.model = tbModel.id INNER JOIN
tbCategory ON tbItemMaster.category = tbCategory.Id INNER JOIN
tbBrand ON tbModel.brand = tbBrand.id inner join tbCompatibleModels on tbItemMaster.Id=tbCompatibleModels.ItemId where tbCompatibleModels.ModelId=' + @ModelId + @Cond + ' Order By UpdatedOn desc;

SELECT *,@reccount as ItemsCount FROM @t_table WHERE SpecialId >=' + @StartRow + ' and SpecialId<@EndRow
ORDER BY UpdatedOn DESC
')

RETURN

can anybody help me pls. ITs very urgent.

Thanks in advance.

View 1 Replies View Related

Dynamic Columns Returned

Sep 6, 2006

I need to write a query that will return an unknown number of columns. Here's the problem:

Let's say you've got a database tracking applications to different university study abroad programs. There may be a dozen or more programs, and the application could be used at any number of different schools, each with different programs. So you have a programs table with ProgramName and ProgramID.

Each program could require a different set things submitted during the application process. So you have a Submissions table, with SubmissionItem and SubmissionID to list the names of each required submission, a Program_Submissions table, with ProgramID and SubmissionID to link Programs to required application items, a SubmittedItems table, that accepts documents uploaded by a user to fullfill the submission requirements, and of course a users table.

I'm not very concerned at the moment about the last two tables. What I need to do is create a report (query) where the first column is the program name, and the other columns are determined by the contents of the Submissions table- if Program_Submissions record exists linking a program to a submission, place a value of 'Y' in that cell, otherwise leave it blank. Any thoughts on how to accomplish this?

I need to do this in a single query that a I can dump into a report builder application to save as a template to give to several people that have been asking for it, and to complicate matters even more, the description above is a little simple, as there are couple different kinds of submissions, with a different table for each.

Any help appreciated. I give rep for good answers!

View 3 Replies View Related

Selecting Dynamic Columns

Jul 12, 2004

I have the following Tables:

Table1: Row1
----------------------------------
Name Gary
Garbage1 A
Garbage2 B

Table2: Row1 Row2 Row3
------------------------------------------------------------------
Name Gary Gary Gary
Value 1 2 3
Desc Day Afternoon Night

Table1 has a 1 to Many relationship with Table2

Id Like to have the select statement Return the data as follows:

Gary,A,B,Day,1,Afternoon,2,Night,3

not like this:
Gary A,B,Day,1
Gary A,B,Afternoon,2
Gary A,B,Night,3

Any Ideas?

View 3 Replies View Related