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


ADVERTISEMENT

Dynamic File Names For Raw Files Created By Conditional Expressions

Feb 26, 2007

Hello,

I have a huge table of around 6.2 billion rows in Oracle and I need to load this table to SQL Server. I am creating multiple raw files based on contact_dates in the table. I have used conditional expressions to branch out and create raw files. I need to specify static file names for each branch or I have to create multiple variables for each branch and use expressions to create file names.

My intention is to create only 1 variable and evaluate the expression based on the conditional expression description. Is this possible?

Regards

RH




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

T-SQL (SS2K8) :: Pivot When Don't Know Amount Of Columns And Column Names

Jan 7, 2015

I am trying to figure out how to pivot a temporary table. I have a table which starts with a date but the number of columns and columns names will vary but will be type INT (Data, col2,col3,col4………….n)

So it could look like

Date , TS-Sales, Budget , Toms sales
01-Jan-14,100,120,300
02-Jan-14,80,150,300
03-Jan-14,100,20,180

Turned to this

01-jan-14, 02-jan-14, 03-jan-14
100,80,100
120,150,20
300,300,180

Or even just the date and a SUM

What I want is to be able to sum al the columns but without knowing the name and the amount columns to start with this is a manually processes. How could I automate this?

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

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

Power Pivot :: New Table Created On Every Refresh?

Dec 21, 2014

My PowerPivot workbook, is generating new tables on each refresh.

When opened up the workbook I find a dozen tables named, "Query1", "Query2", "Query3" etc.

It's pulling data from SQL Server.

View 4 Replies View Related

Referencing Colum Names In Recordset Which Is Created By Join - URGENT!

Jul 30, 2006

Urgent help needed!I moved an application from ASP+ACCESS to ASP+MS SQLSERVER and I havethe following problem:If the join on two tables results on duplicate colum names (whichappear in both tables) I could reference them by using:RECORDSET("TABLENAME.COLUMNAME")However with SQLServer if I try this kind of reference I get an errormessage.How can between two colums with the same name from two differen tables?Thanks in advance!Adam

View 5 Replies View Related

Power Pivot :: Relationship Cannot Be Created Because Each Column Contains Duplicate Values

Nov 15, 2011

I have 2 excel tables files.One table has info about sales by country, by model, by date...and the other table has units by date.Obviously, the common key is "date"....and by creating a relationship, I can add "units" to my combined resulting table.However, I can't create the relationship. Excel keeps telling me: "the relationship cannot be created because each column contains duplicate values. Select at least one column that contains only unique values".

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

SQL Server 2014 :: Pivot Table With Column Names To Rows?

Aug 1, 2015

I have a table with following rows.

FY REVCODE Jul Jun
2015 BNQ 1054839 2000000
2015 FNB 89032 1000000
2015 RS 1067299 3000000

I am looking to convert it to

Month BNQ FNB RS
JUL 1054839 89032 1067299
JUN 2000000 1000000 3000000

I tried with the following and result is coming for one month i.e. JUL but not with the second Month i.e Jun

SELECT 'Jul1' AS MON, [BNQ], [FNB], [RS]
FROM
(SELECT REVENUECODE, SUM(ROUND(((Jul/31)*30),0)) AS JUL
FROM RM_USERBUDGETTBL
WHERE USERNAME='rahul' AND FY=2015
GROUP BY REVENUECODE, USERNAME
) AS SourceTable
PIVOT
(SUM(JUL) FOR REVENUECODE IN ([BNQ], [FNB], [RS])) AS PivotTable

Results:

MONTHBNQ FNB RS
Jul11054839 89032 1067299

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

Power Pivot :: Can Create A New Table That Shows Only Distinct Names

May 5, 2015

My DB contains company names repeating themselves several times (in the same column).

Can I create a new table that shows only the distinct names, and use it to work with the data?

My intention is to allow my users to choose only from the options within the DB (mimic in a way the list validation option in excel)

View 6 Replies View Related

Pivot Table In AdventureWorks: Invalid Column Names DepartmentID And ShiftID

Jan 3, 2008

Hi all,
I executed the following T-SQL code from a tutorial book and executed it in my SQL Server Management Studio Express (SSMSE):
--PivotTable.sql--
USE Adventureworks

GO

SELECT ShiftID, Name

FROM HumanResources.Shift

SELECT EmployeeID, ShiftID, Name

FROM HumanResources.Employee, HumanResources.Department

WHERE Employee.DepartmentID = Department.DepartmentID

--Compute the number of employees by

--department name and shift

SELECT Name, [1] AS 'Day', [2] AS 'Evening',

[3] AS 'Night'

FROM

(SELECT e.EmployeeID, edh.ShiftID, d.Name

FROM HumanResources.Employee e

JOIN HumanResources.EmployeeDepartmentHistory edh

ON e.EmployeeID = edh.EmployeeID

JOIN HumanResources.Department d

ON edh.DepartmentID = d.DepartmentID) st

PIVOT

(

COUNT (EmployeeID)

FOR ShiftID IN

( [1], [2], [3])

) AS spvt

ORDER BY Name

--For display in book

SELECT Name, [1] AS 'Day', [2] AS 'Evening',

[3] AS 'Night'

FROM

(SELECT e.EmployeeID, edh.ShiftID, CAST(d.Name AS nvarchar(26)) 'Name'

FROM HumanResources.Employee e

JOIN HumanResources.EmployeeDepartmentHistory edh

ON e.EmployeeID = edh.EmployeeID

JOIN HumanResources.Department d

ON edh.DepartmentID = d.DepartmentID) st

PIVOT

(

COUNT (EmployeeID)

FOR ShiftID IN

( [1], [2], [3])

) AS spvt

ORDER BY Name



IF EXISTS(SELECT name FROM sys.tables WHERE name = 'pvt')

DROP TABLE pvt

GO

--Create a table that saves the result of a pivot with employee

--names instead of numbers for column values

SELECT VName, [164] 'Mikael Q Sandberg', [198] 'Arvind B Rao',

[223] 'Linda P Meisner', [231] 'Fukiko J Ogisu'

INTO pvt

FROM

(SELECT PurchaseOrderID, EmployeeID, v.Name as 'VName'

FROM Purchasing.PurchaseOrderHeader h

JOIN Purchasing.Vendor v

ON h.VendorID = v.VendorID) p

PIVOT

(

COUNT (PurchaseOrderID)

FOR EmployeeID IN

( [164], [198], [223], [231], [233] )

) pvt

ORDER BY VName

GO

--Show an excerpt FOR VName starting with A

SELECT TOP 5 * FROM pvt

WHERE VName LIKE 'A%'

GO

--For display in book

SELECT TOP 5 CAST(VName AS NVARCHAR(22)) 'VName',

[Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu]

FROM pvt

WHERE VName LIKE 'A%'

GO

--VendorID for Advanced Bicycles is 32

--Four PurchaseOrderID column values exist in PurchaseOrderHeader

--with VendorID values of 32 and EmployeeID values of 164

SELECT VendorID, Name FROM Purchasing.Vendor WHERE Name = 'Advanced Bicycles'

SELECT PurchaseOrderID FROM Purchasing.PurchaseOrderHeader WHERE VendorID = 32 and EmployeeID = 164

--Unpivot values

SELECT TOP 8 VName, Employee, OrdCnt

FROM

(SELECT VName, [Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu]

FROM pvt) p

UNPIVOT

(OrdCnt FOR Employee IN ([Mikael Q Sandberg],

[Arvind B Rao], [Linda P Meisner], [Fukiko J Ogisu])

)AS unpvt

GO

--For display in book

SELECT TOP 8 CAST(VName AS nvarchar(28)) 'VName', CAST(Employee AS nvarchar(18)) 'Employee', OrdCnt

FROM

(SELECT VName, [Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu]

FROM pvt) p

UNPIVOT

(OrdCnt FOR Employee IN

([Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu])

)AS unpvt

GO



--Query to check unpivoted values

SELECT TOP 2 *

FROM pvt

ORDER BY VName ASC

GO

--For display in book

SELECT TOP 2 CAST(VName AS NVARCHAR(22)) 'VName',

[Mikael Q Sandberg], [Arvind B Rao],

[Linda P Meisner], [Fukiko J Ogisu]

FROM pvt

ORDER BY VName ASC

GO





IF EXISTS(SELECT name FROM sys.tables WHERE name = 'pvt')

DROP TABLE pvt

GO

========================================
I got the following error messages and results:

Msg 207, Level 16, State 1, Line 7

Invalid column name 'DepartmentID'.

Msg 207, Level 16, State 1, Line 5

Invalid column name 'ShiftID'.

(86 row(s) affected)

(5 row(s) affected)

(5 row(s) affected)

(1 row(s) affected)

(4 row(s) affected)

(8 row(s) affected)

(8 row(s) affected)

(2 row(s) affected)

(2 row(s) affected)

=================================================
I do not know why I got these 2 errors and how to correct them. Please help and advise me how to correct the mistakes and obtain the completely printed-out correct results.

Thanks in advance,
Scott Chang

View 3 Replies View Related

Dynamic Pivot

Oct 30, 2006

Hi

Is there a way for using the PIVOT synatx with a dyamic/unknown list of columns? In most PIVOT examples ther columns are "hard" coded but if for example, you are pivoting a sales order table where u don't know which Fiscal Years the orders cover, how could you do this?

Thanks







View 3 Replies View Related

Dynamic Column Names In A SP

Jan 1, 2008

Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query.  Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance 

View 2 Replies View Related

Dynamic Column Names

Jun 10, 2004

Hi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'

select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1

View 2 Replies View Related

Dynamic Column Names

Jan 19, 2005

thanx for the help in advance,

i have a table with Monday, Tuesday, Wednesday. .... Sunday

i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday

but when i do something like

select top 2 * from myTable
where 'myTable.'+@stat = 1

which is as same as

select top 2 * from myTable
where myTable.wednesday = 1

i receive a syntax error. how can i dynamically select myTable.Wednesday ?

i also tried using
select top 2 * from Intercon
where '@stat' = '1'

but then this doesn't return anything where it should.
any help?

View 2 Replies View Related

Dynamic SQL And Table Names

Jun 5, 2008

Hi all,

I have been attempting to construct some dynamic SQL using a table name as a parameter, and call it using sp_executesql, in SQL Server 2005.

According to all of the searches and articles I have read today, the following should work:

DECLARE @rownumINT
DECLARE @tabNamNVARCHAR(100)
DECLARE @SQLStringNVARCHAR(500)
DECLARE @ParmDefinition NVARCHAR(500)

SET @rownum = 35
SET @tabNam = 'AdminScriptHistory'

SELECT @SQLString = N'SELECT * FROM @table where executionorder=@row'
SET @ParmDefinition = N'@table VARCHAR(100), @row INT'

EXEC sp_executesql @SQLString, @ParmDefinition, @tabNam,@rownum

However, it fails with:

Msg 1087, Level 15, State 2, Line 1
Must declare the table variable "@table".

Anybody know why?

Cheers

View 1 Replies View Related

Dynamic Column Names

Aug 28, 2007

Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.

Mike
"oh, that monkey is going to pay"

View 3 Replies View Related

Dynamic Column Names

Aug 9, 2007

Hi guys 'n gals,

I am trying to achieve something a bit complex (or at least appears to be for me).

I have the following table structure:

UID, GroupID, ColumnName, ColumnValue

here is some example data:

UID, GroupId, ColumnName, ColumnValue
1, 1, MAC Address, 8a7sd87sad
2, 1, IP Address, 192.168.1.100
3, 1, Name, John
4, 2, MAC Address, 09a8sd098as
5, 2, Name, Steven

and here is what I would like the example to come out like:


GroupID, MAC Address, IP Address, Name
1, 8a7sd87sad, 192.168.1.100, John
2, 09a8sd098as, NULL, Steven

It needs to be completely dynamic though, as a new column name could be entered to the source table at any time...

I have tried Dynamic SQL and JOINs, but can only seem to get it to work correctly. I was starting to consider doing a loop similar to this:





Code Snippet

DECLARE @ColumnName varchar(128)
DECLARE @Sql varchar(255)
SELECT @ColumnName = [ColumnName] FROM tblSourceData
SELECT @Sql = 'SELECT [GroupId], [ColumnValue] AS ['+@ColumnName+'] FROM tblSourceData'
EXEC (@Sql)

Could somebody please point me in the right direction? I've heard a bit about Pivot tables in my search for this solution, is that perhaps the route I need to go?

Regards,
Justin

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

Crosstab Qry With Dynamic Field Names

Jul 20, 2005

Hi,I am trying to create a stored procedures (SQL 7.0), to provide dataina crosstab format.(I'm using Crystal Reports 8.5, but the Crosstab capabilities areterrible, so I have to do as much as possible on the SQL side)I have a table [Occurrences] with the following fields:Year (int)Month (int)Occurs (int)Claims (int)I need a query to give me the following format:Acct_Month 2001 2002 2003Occurs Claims Occurs Claims Occurs ClaimsJanuary 120 180 132 196 110 140February 154 210 165 202 144 178March etc.......Catch! I need the Year field name to be the contents of the fieldYear in the Table (2001, 2002, 2003...). Not the usual Year_1, Year_2approach.I got the month name ok...Acct_Month = DATENAME(month, Convert(Varchar(2), Month) + '/01/'+Convert(Char(4),Year))Is it possible to do this easely, without the use of cursors?Any help would be much appreciated.Luis Pinto

View 1 Replies View Related

Working With Dynamic File Names

Jul 28, 2006

I am working with an ftp task which is using a file connection to send a file to a remote server. This file is going to have a dynamic name (e.g. "Orders for 2006-07-29.txt"). How can I dynamically specify the file path/name for the file connection? You can't specify a varaible through the ui.

I would imagine that you can do this through a script task but I'm not sure how to reference the connection object. any help would be greatly appreciated.

thanks,

Scott

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

How To Change Output With Dynamic Pivot

Apr 2, 2015

This is my table.

CREATE TABLE tpivot
([col1] varchar(80), [col2] varchar(80), [col3] varchar(80), [col4] varchar(80), [col5] varchar(80)) ;
INSERT INTO tpivot
([col1], [col2], [col3], [col4], [col5])
VALUES

[code]...

My goal is to turn the table so that the output look like this.

col1 col2 col3 col4 col5
Datum 01.12.2014 02.12.2014 03.12.2014 04.12.2014
EC -204.9 -352.9 -105 -371
Mastercard -88 0 -182 -112.9
Postfinance 0 -79.9 0 -751
VISA -19 -20 -436 -346

I need to have a dynamic pivot since i import the table from a csv that could have different amount of columns each time. I can't even get a static pivot to work.

View 1 Replies View Related







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