SQL Server 2008 :: Dynamic Conversion Of Pivot Columns

Jun 15, 2015

I have attached SQL File which Gives me the below resultset Excel.xlsx

But the problem is i am not able to round off the dynamic columns in side my PIVOT, how to rewrite the dynamic query.

View 4 Replies


ADVERTISEMENT

SQL Server 2008 :: Dynamic Query Pivot Values Change

Aug 6, 2015

I have below script

CREATE TABLE dbo.TestPivot(
CollectionDate DATETIME,
Products VARCHAR(40),
ItemCount INT
)
INSERT INTO dbo.TestPivot
SELECT '4/1/2015','Benz' , 20

[Code] ....

-- Original Output
ProductsApr 2015May 2015Jun 2015
Benz10-800NULL
Toyota5NULL-180

****Required output where ever we have negative values we need to display message Invalid out put message for those negative rows

ProductsApr 2015May 2015 Jun 2015
Benz10Invalid NULL
Toyota5NULL Invalid

View 2 Replies View Related

SQL Server 2014 :: Pivot IN Clause - Dynamic Columns

May 12, 2015

The first select is running fine but due to extra values added to the table the list of manual difined columns must be added manualy each time new values occur.

Is it possible to make the PIVOT's IN clause dynamicly as stated in the second script (it is based on the same table #source) when running it prompts the next error;

Msg 156, Level 15, State 1, Line 315
Incorrect syntax near the keyword 'select'.
Msg 102, Level 15, State 1, Line 315
Incorrect syntax near ')'.

adding or moving ')' or '(' are not working.......

select *
into #temp
from #source
pivot ( avg(value) for drive in ([C], [D], [E], [F], [G], [H], [T], [U], [V] )) as value
select * from #temp order by .........

versus

select *
into #temp
from #source
pivot ( avg(value) for drive in (select distinct(column) from #source)) as value

select * from #temp order by .....

View 3 Replies View Related

SQL Server 2012 :: Dynamic Table Pivot With Multiple Columns

Jan 23, 2014

I am trying to pivot table DYNAMICALLY but couldn't get the desired result .

Here is the code to create a table

create table Report
(
deck char(3),
Jib_in float,
rev int,
rev_insight int,
jib_out float,

[Code] .....

Code written so far. this pivots the column deck and jib_in into rows but thats it only TWO ROWS i.e the one i put inside aggregate function under PIVOT function and one i put inside QUOTENAME()

DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX);
SET @columns = N'';
SELECT @columns += N', p.' + QUOTENAME(deck)
FROM (SELECT p.deck FROM dbo.report AS p
GROUP BY p.deck) AS x;

[Code] ....

I need all the columns to be pivoted and show on the pivoted table. I am very new at dynamic pivot. I tried so many ways to add other columns but no avail!!

View 1 Replies View Related

SQL Server 2012 :: How To Join Pivot Results With Dynamic Columns

Mar 5, 2015

I have a lookup table, as below. Each triggercode can have several service codes.

TriggerCodeServiceCode
BBRONZH BBRZFET
BBRONZH RDYNIP1
BBRONZP BBRZFET
BCSTICP ULDBND2
BCSTMCP RBNDLOC

I then have a table of accounts, and each account can have one to many service codes. This table also has the rate for each code.

AccountServiceCodeRate
11518801DSRDISC -2
11571901BBRZFET 5
11571901RBNDLOC 0
11571901CDHCTC 0
17412902CDHCTC1 0
14706401ULDBND2 2
14706401RBNDLOC 3

What I would like to end up with is a pivot table of each account, the trigger code and service codes attached to that account, and the rate for each.

I have been able to dynamically get the pivot, but I'm not joining correctly, as its returning every dynamic column, not just the columns of a trigger code. The code below will return the account and trigger code, but also every service code, regardless of which trigger code they belong to, and just show null values.

What I would like to get is just the service codes and the appropriate trigger code for each account.

SELECT @cols = STUFF((SELECT DISTINCT ',' + ServiceCode
FROM TriggerTable
FOR XML PATH(''), TYPE
).value('(./text())[1]', 'VARCHAR(MAX)')
,1,2,'')

[Code] ....

View 1 Replies View Related

SQL Server 2012 :: Convert Rows To Columns Using Dynamic PIVOT Table

Feb 3, 2015

I have this query:

SELECT TOP (100) PERCENT dbo.Filteredfs_franchise.fs_franchiseid AS FranchiseId, dbo.Filteredfs_franchise.fs_brandidname AS Brand,
dbo.Filteredfs_franchise.fs_franchisetypename AS [Franchise Type], dbo.Filteredfs_franchise.fs_franchisenumber AS [Franchise Number],
dbo.Filteredfs_franchise.fs_transactiontypename AS [Transaction Type], dbo.Filteredfs_franchise.fs_franchisestatusname AS [Status Code],

[Code] ....

I need to pivot this so I can get one row per franchiseID and multiple columns for [Franchisee Name Entity] and [Franchise Name Individual]. Each [Franchisee Name Entity] and [Franchise Name Individual] has associated percentage of ownership.

This has to be dynamic, because each FranchiseID can have anywhere from 1 to 12 respective owners and those can be any combination of of Entity and Individual. Please, see the attached example for Franchise Number 129 (that one would have 6 additional columns because there are 3 Individual owners with 1 respective Percentage of ownership).

The question is how do I PIVOT and preserve the percentage of ownership?

View 3 Replies View Related

SQL Server 2012 :: Dynamic Pivot Statement To Calculate And Organize Columns

Mar 11, 2015

How to write a Dynamic Pivot Statement to Calculate and Organize Columns like:

CREATE TABLE #mytable
(
Name varchar(50),
GA int,
GB int,
startdate DATETIME,
enddate DATETIME

[Code] ...

Below is Our Sample Table Data.

Name GAGBstartdateenddate
Pavan 261/1/20151/1/2015
Hema 561/1/20151/1/2015
Surya 501/1/20151/1/2015
Pavan 811/2/20151/8/2015
Hema 311/2/20151/8/2015
Surya 121/2/20151/8/2015
Pavan 1041/9/20151/15/2015
Hema 301/9/20151/15/2015
Surya 6131/9/20151/15/2015

How to write Pivot Satement to get Oupt like below:

1/1/2015 Pavan Hema Surya SumTotal
Total 8 11 5 24
GA 2 5 5 12
GB 6 6 0 12

1/8/2015 Pavan Hema Surya SumTotal
Total 9 4 3 16
GA 8 3 1 12
GB 1 1 2 4

1/15/2015 Pavan Hema Surya SumTotal
Total 14 3 19 36
GA 10 3 6 19
GB 4 0 13 17

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 2008 :: How To Pivot Unknown Number Of Rows To Columns Using Data As Column Headers

Sep 10, 2015

I have a single table that consist of 4 columns. Entity, ParamName, ParamsValue and ParamiValue. This table stores normalized Late Fee related parameters for apartments. The Entity field contains a code that identifies the apartment complex. The ParamName in a textual field that contains the name of the parameter that the other 2 fields define the value for; ParamsValue and ParamiValue. If the Late Fee parameter (as named in ParamName is something numerical then the value for that parameter can be found in ParamiValue else its in ParamsValue.

I don't know if 'Pivot' is the correct term to use for describing what I am trying to do because I've looked at the Pivot examples and I don't see how that will work for this. Using the Table and data as provided below, how would I construct a query so that I get 1 row per Entity in which the columns are the ParamsValue or ParamiValue for the ParamName listed in the column header (for the query)?

Below is the DDL to create the table and populate it.

USE [DBA_UTIL]
CREATE TABLE [dbo].[PARAMEXAMPLE](
[Entity] [varchar](16) NULL,

[Code]....

View 4 Replies View Related

SQL Server 2008 :: Data Conversion - Merge Multiple Columns Into Single Column Separated By Semicolons

Oct 19, 2015

I'm working on a script to merge multiple columns(30) into a single column separated by a semicolons, but I'm getting the following error below. I tried to convert to the correct value. but I'm still getting an error.

Error: "Conversion failed when converting the varchar value ';' to data type tinyint".

select
t1.Code1TypeId + ';' +
t1.Code2TypeId + ';' +
t1.Code3TypeId + ';' +
t1.Code4TypeId as CodeCombined

from Sampling.dbo.account_test t1

where t1.Code1TypeId = 20
or t1.Code2TypeId = 20
or t1.Code3TypeId = 20
or t1.Code4TypeId = 20

View 4 Replies View Related

Pivot On Dynamic Columns

May 11, 2006

I have a table with 40k terms and I need to map these to a set of objects where each object is represented as a column(tinyint). The object/column name is represented as a guid and columns are added/removed dynamically to support new objects for a set of terms.

I can get the rows needed:

guid1guid2guid3guid4guid5
================================
01100
01101

I think I need to then convert this set of rows to a table which I can join to the object runtime table to start these objects if the column has a count/sum greater than 0. This is the table I think I need in order to join on guids to the runtime table:

NAME Count
===========
guid10
guid22
guid32
guid40
guid51

I don't know how to construct this table for the former table. I think it may be a pivot table, but I don't know. I have the column names:

SELECT NAME
FROM SYSCOLUMNS
WHERE ID = OBJECT_ID(#Temp)
ORDER BY COLID

NAME is a sysname, which doesn't seem to cast into a guid, also a problem when joining the runtime table with this #Temp table.

I also don't want to use a cursor to construct a table.


Thanks for any help,
James

View 1 Replies View Related

Help With A Pivot Table (dynamic Columns)

Feb 15, 2008

I have the following Pivot Table:



Code Snippet
Declare @tblEquipment Table
(
numEquipmentID INT,
txtManufacturer nvarchar(30),
txtModel nvarchar(30)
)
Declare @tblEquipmentFields Table
(
numFieldNameID INT,
txtFieldName nvarchar(25)
)
Declare @tblEquipmentDetails Table
(
numEquipmentDetailsID INT,
numEquipmentID INT,
numFieldNameID INT,
txtFieldValue nvarchar(30)
)
Insert INTO @tblEquipment Values(23, 'Dell', 'Optiplex 270')
Insert INTO @tblEquipment Values(26, 'Dell', '1705FP')
Insert INTO @tblEquipment Values(42, 'Dell', 'Optiples 745')
Insert INTO @tblEquipmentFields Values(1, 'Monitor Size')
Insert INTO @tblEquipmentFields Values(2, 'Processor Type')
Insert INTO @tblEquipmentDetails Values(1077, 23, 2, 'P4M')
Insert INTO @tblEquipmentDetails Values(1146, 26, 1, '17')
Insert INTO @tblEquipmentDetails Values(1026, 42, 2, 'P4 Dual Core')
Select numEquipmentID As EquipmentID, [Monitor Size], [Processor Type]
From
(Select a.numEquipmentID, txtManufacturer, txtModel, txtFieldName, txtFieldValue
From @tblEquipment a JOIN
@tblEquipmentDetails b ON
a.numEquipmentID = b.numEquipmentID
JOIN @tblEquipmentFields c ON
b.numFieldNameID = c.numFieldNameID
) As SourceTable
Pivot
(
Max(txtFieldValue)
For txtFieldName IN ([Monitor Size], [Processor Type])
) As PivotTable






What I'm wondering is if it's possible to have the columns change dynamically. For example:

If lets say I only want the record with numEquipmentID of 23 to show I only want its corresponding information to show

EquipmentID ProcessorType
23 P4M

Now lets say that I want to bring back an additional record, like 23 and 26 I would like the columns to change to the following

EquipmentID ProcessorType Monitor Size
23 P4M NULL
26 NULL 17

So in essence a column will be added based on the equipmentID. Thanks in advanced.

View 4 Replies View Related

PIVOT With Dynamic Columns Names Created

Aug 3, 2007

I am trying to do a PIVOT on a query result, but the column names created by the PIVOT function are dynamic.

For example (modified from the SQL Server 2005 Books Online documentation on the PIVOT operator) :

SELECT
Division,
[2] AS CurrentPeriod,
[1] AS PreviousPeriod
FROM
(
SELECT
Period,
Division,
Sales_Amount
FROM
Sales.SalesOrderHeader
WHERE
(
Period = @period
OR Period = @period - 1
)
) p
PIVOT
(
SUM (Sales_Amount)
FOR Period IN ( [2], [1] )
) AS pvt

Let's assume that any value 2 is selected for the @period parameter, and returns the sales by division for periods 2 and 1 (2 minus 1).

Division CurrentPeriod PreviousPeriodA 400 3000 B 400 100 C 470 300 D 800 2500 E 1000 1900

What if the value @period were to be changed, to say period 4 and it should returns the sales for periods 4 and 3 for example, is there a way I can change to code above to still perform the PIVOT while dynamically accepting the period values 4 and 3, applying it to the columns names in the first SELECT statement and the FOR ... IN clause in the PIVOT statement ?

Need a way to represent the following [2] and [1] column names dynamically depending on the value in the @period parameter.

[2] AS CurrentPeriod,
[1] AS PreviousPeriod

FOR Period IN ( [2], [1] )

I have tried to use the @period but it doesn't work.

Thanks in advance.

Kenny

View 1 Replies View Related

Transact SQL :: SSMS Pivot With Dynamic Columns

Apr 23, 2015

I found this Microsoft article for creating crosstab-like queries in SSMS.Is it possible, however, to create this same query if I do not know what the values for the columns will be?  Using their example for my problem, I will not know what the values in the "IN" criteria will be because my query would be for a "rolling" 12 months (thus causing that IN criteria to change every month).I've tried declaring variables to pull in the values, but since this will eventually go into a view, I don't think that I can use declared variables.

View 3 Replies View Related

SQL Server 2012 :: Dynamic Value On Pivot

Sep 21, 2015

Can we pass dynamic values while pivoting?

Here is example

Declare @a date, @b date
set @a='2015-09-08 22:19:29.330'
set @b='2015-09-17 22:19:29.330'
create table #DateTemp(Full_Date_Text_YYYY_MM_DD datetime,Full_Date date)
insert into #DateTemp(Full_Date_Text_YYYY_MM_DD,Full_Date)
select '2015-09-09 00:00:00.000','2015-09-09'

[Code] ......

View 3 Replies View Related

SQL Server 2012 :: Dynamic Pivot With Subgrouping

Nov 25, 2013

I am trying to pivot some data as you would normally however I am trying to also group the pivot into three sub column groups too.

Basically the scenario is that I have three sub groups Budget, Person, RenewalDate for each Service (Service being the pivot point). So for each unique service I want to display the budget person and renewal date for each service by company.

I have created two tables to illustrate the base data and the required output.

How to do this dynamically because the number of Services is unknown, i.e. it could be 4 Services or 20, each with three sub columns, budget, person and renewal date.

Please find code below. It should be quite self explanatory as to what I am trying to do.

IMPORTANT:

1. I really need it to be dynamic
2. the Services are not standardised names, they are numbered for illustration purposes only, they vary in naming convention.

create table #BaseData
(
Company nvarchar(100),
Person nvarchar(50),
[Service] nvarchar(100),
Budget int,
RenewalDate datetime
)

[Code] .....

View 4 Replies View Related

SQL Server 2012 :: Join Two Dynamic Pivot Tables

Dec 11, 2013

I have two dynamic pivot tables that I need to join. The problem I'm running into is that at execution, one is ~7500 characters and the other is ~7000 characters.

I can't set them both up as CTEs and query, the statement gets truncated.

I can't use a temp table because those get dropped when the query finishes.

I can't use a real table because the insert statement gets truncated.

Do I have any other good options, or am I in Spacklesville?

View 7 Replies View Related

SQL Server 2012 :: Dynamic Pivot Table Not Grouping

Mar 26, 2014

I have a query

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
DECLARE @ColumnName AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
+ QUOTENAME(name)

[Code] ....

and so on.

I've tried putting my group by everywhere but it's not working. I'm missing something but what?

View 9 Replies View Related

SQL Server 2012 :: Creating Dynamic Pivot Table

Jul 2, 2014

I am having trouble figuring out why the following code throws an error:

declare
@cols nvarchar(50),
@stmt nvarchar(max)
select @cols = ('[' + W.FKStoreID + ']') from (select distinct FKStoreID from VW_PC_T) as W
select @stmt = '
select *

[Code] ...

The issue that I am having is:

Msg 245, Level 16, State 1, Line 4
Conversion failed when converting the varchar value '[' to data type int.

I know that I have to use the [ ] in order to run the dynamic sql. I am not sure what is failing and why as the syntax seems to be clean to me (obviously it is not).

View 6 Replies View Related

SQL Server 2008 :: Pivot Data In Table?

Jun 24, 2015

MyTable has the following data (Just a sample)

parent | NAme | Checked | contactmethod|Check2 | Other
974198 | Employment | true | Face to Face | true | null
974224 | Other | true | Face to Face | true | skills
974224 | Other | true | Contact | true | skills

I'd like to pivot on "parent"

In a perfect world I'd like to see output like

974198 | Employment | true | Face to Face | true | null

974224 | Other | true | Face to Face, Collateral Contact | true | skills

If there are more than one name or contactmethod for the same parent then they would be strung along with commas

View 1 Replies View Related

SQL Server 2008 :: How To Merge Two Rows Into One (conditions Or Pivot)

Jul 8, 2015

Merge two rows into one (conditions or Pivot?)

I have Temptable showing as

Columns:EmpName, Code, Balance

Rows1: EmpA, X, 12
Rows2: EmpA, Y, 10

I want to insert the above temp table to another table with column names defined below like this

Empname, Vacation Hours, Sicks Hours
EmpA, 12, 10

Basically if it is X it is vacation hours and if it is Y it is sick Hours. Needs a simple logic to place the apprpriate hours(Balance) to its respective columns. I'm not sure how I can achieve this in using Pivot or Conditions.

View 3 Replies View Related

SQL Server 2012 :: Creating A View Or Procedure From Dynamic Pivot Table

May 29, 2015

I have written a script to pivot a table into multiple columns.

The script works when run on its own but gives an error when i try to create a view or aprocedure from the same script. The temporary table #.... does not work so i have converted it to a cte.

Here is a copy of the script below

-- Dynamic PIVOT
IF OBJECT_ID('#External_Referrals') IS NULL
DROP TABLE #External_Referrals;
GO
DECLARE @T AS TABLE(y INT NOT NULL PRIMARY KEY);

[Code] ....

View 7 Replies View Related

SQL Server 2008 :: Pivot Function - Additional Final Column

Mar 11, 2015

I've managed to use the pivot function using the code below which gives me the following output:

Location_Code Gt 0-1 Gt 1-2 Gt 2-3 Gt 3-4
North 10 0 3 5
West 6 3 2 0
South 4 2 8 2

This is exactly what I want but I would like an additional final column that will total the columns by location_code and weekband.

[Location_Code] AS 'Location Code'
,[Gt 0-1], [Gt 1-2], [Gt 2-3], [Gt 3-4]
from (select [WeekBand]
,[Location_Code]
, count(*) as CountOf

[Code] ....

View 7 Replies View Related

SQL Server 2008 :: Conversion Failed When Converting Nvarchar Value To INT

Apr 9, 2015

Declare @specificDB nvarchar(max) = 'AdventureWorksDW2012'
,@tablename nvarchar(max) = 'DimAccount'

declare @localtab INT
SET @localtab = (SELECT Convert(INT,('select count(*) from ' + @specificDB+'.'+'INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = ''BASE TABLE'' AND Table_name = ' + @tablename)))
Print @localtab
Print @localtab

----

Msg 245, Level 16, State 1, Line 8
Conversion failed when converting the nvarchar value 'select count(*) from AdventureWorksDW2012.INFORMATION_SCHEMA.Tables WHERE TABLE_TYPE = 'BASE TABLE' AND Table_name = DimAccount' to data type int.

View 2 Replies View Related

SQL Server 2008 :: Conversion Failed When Converting Varchar Value - Error

Sep 24, 2015

I am getting the following message when trying to run the below sql

Msg 245, Level 16, State 1, Line 15

Conversion failed when converting the varchar value 'Select COUNT(*) From Adventures.dbo.tblLibrary' to data type int.

Declare @Datestart as Date
Declare @CntBefore as int
Declare @CntNow as int
Declare @Database as varchar(30)
Declare @TableName as varchar(60)

[Code] ....

View 9 Replies View Related

URGENT Plz ! Pivot Rows To Columns In SQL Server 2000

May 16, 2007

Hi,

I have the following data in a table called "Products" :

Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 1 R
Panel_1 1 L
Panel_1 1 R
Panel_1 1 R
Panel_2 1
Panel_2 1 L


I need to group this data like this :

Product Qty LHinging RHinging
-------- ----- -------- --------
Panel_1 3 L R
Panel_2 1
Panel_2 1 L


How can I do that ???

Thanks !

View 3 Replies View Related

SQL Server 2012 :: Pivot Rows And Columns In The Same Query?

Mar 26, 2015

I currently have data stored in a temporary table and I would like to transpose the data into a better format. I would like for the query to be dynamic since one of the tables currently has over 500 columns.

The attached file provides an example of the table structure along with sample data. Below the first set of data is the desired final format.

View 2 Replies View Related

SQL Server 2012 :: Transpose (Pivot) Columns To Rows?

Aug 15, 2015

I need to pivot my records in columns into rows. Here is sample data:

create table #columnstorows
(
Payervarchar (5),
IDvarchar (5),
ClMvarchar (2),
Paid1float,

[Code] ....

Desired result:

Payer1Payer2ID1ID2Paid1APaid1BPaid2APaid2BPaid3APaid3B
U001 U002 00100276.58 19.53 153.4896.43 53.48 200

View 6 Replies View Related

SQL Server 2012 :: Rows To Columns Conversion

Aug 28, 2014

I would like to reach out in converting rows to cols.

Format:
NodeName|upsBasicIdentName|upsBasicIdentModel | upsAdvIdentSerialNumber | upsAdvIdentDateOfManufacture | upsBasicBatteryLastReplaceDate

I have this query. the values of each are under status.

SELECT
Nodes.Caption AS NodeName, CustomNodePollers_CustomPollers.MIB AS MIB, CustomNodePollerStatus_CustomPollerStatus.Status AS Status

FROM

((Nodes INNER JOIN CustomPollerAssignment CustomNodePollerAssignment_CustomPollerAssignment ON

[Code] ....

View 3 Replies View Related

SQL Server 2008 :: Conversion Of Varchar Data Type To Datetime Resulted In Out Of Range Value

May 14, 2015

I have problem to execute query with interval date.

If try this query I don't have problem and the output is correct:

SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '01/01/2015', 121)
AND CONVERT (datetime, '09/01/2015', 121);Instead if try this I have error:
SELECT * FROM dotable
WHERE
dotableDate BETWEEN CONVERT (datetime, '25/01/2015', 121)
AND CONVERT (datetime, '28/01/2015', 121);

[Err] 22007 - [SQL Server]The conversion of a varchar data type to a datetime data type resulted in an out-of-range value.

View 3 Replies View Related

SQL Server 2014 :: UNION Vs PIVOT For De-flattening SOME Columns Of A Partially Flat Table

Jan 23, 2015

We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:

SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
UNION
SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>

I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns. Performance of UNION good, but another person here chose to use PIVOT instead.I can' find any articles on PIVOT vs. UNION for "de-flattening".

View 2 Replies View Related

SQL Server 2008 :: Dynamic Dataset And Tables In SSRS

Jul 27, 2015

Need to change a Excel report to SSRS.

Excel report has around 15 tables all with different columns.

Is there a way , I can show all data in SSRS by avoiding creation of 15 datasets and 15 tables.

Note -- All 15 tables have differnt columns list.

View 3 Replies View Related

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 View Related







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