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


ADVERTISEMENT

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

Getting Table Names From AdventureWorks Database

Apr 24, 2008

Hello,
I have ran into a problem when i'm getting table names in c#.
I have tried the sql command:
select name from sysobjects where type='U' order by name
So it returns the names without a problem. But when I try to access to some tables using their names (to retrieve the columns) I sometimes get the error "Invalid object name" following by the name of the table.
So I realized the if the table was not under dbo user then i would get this error.
for example: "select name from ContactCreditCard"
 gives me error because in database management tool it is shown like this: Sales.ContactCreditCard
How can i fix this problem? How to get that prefix of the tables?
 
Thank you,
 

View 8 Replies View Related

Transact SQL :: Pivot And Invalid Column Name

May 13, 2015

I'm trying to Pivot and I keep getting an "Invalid Column Name" error, which I can't figure out since, if I run the query and exclude the Pivot statement, the query runs fine.

Columns
ItemNmbr Char(31) not null
SetupTime_I Numeric(19,5) not null
WCID_I  Char(11) not null
select ItemNmbr,SetupTime_I, WCID_I from RT010130
Results

Now run
select ItemNmbr,SetupTime_I, WCID_I
from RT010130
pivot (sum(SetupTime_I) for WCID_I in ([BLA01],[URE02])) PVT

And I get an Invalid Column Name error for both SetupTime_I and WCID_I - which, as far as I can tell, is demonstrably incorrect.

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

Integration Services :: Export Column Transform Says File Names Are Invalid Or A Device

Apr 29, 2015

We run std 2008 r2.  I'm looking at the files this transform is complaining about.   They seem to be named appropriately.  The customerid folders don't exist when this runs.  I'm going to put one in place to see if that is the problem.

The errors i'm getting are...

[Export Column [22]] Error: The file name "c:usersmyuserid heprojectnamecustomeridafilename.doc" is not valid. The file name is a device or contains invalid characters.
[Export Column [22]] Error: SSIS Error Code DTS_E_INDUCEDTRANSFORMFAILUREONERROR.  The "component "Export Column" (22)" failed because error code 0xC020207F occurred,

and the error row disposition on "input column "FILENAME" (29)" specifies failure on error. An error occurred on the specified object of the specified component. 

There may be error messages posted before this with more information about the failure.

[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PROCESSINPUTFAILED.  The ProcessInput method on component "Export Column" (22) failed with error code 0xC0209029

while processing input "Export Column Input" (23). The identified component returned an error from the ProcessInput method. The error is specific to the component,but the error is fatal and will cause the Data Flow task to stop running.  There may be error messages posted before this with more information about the failure.

View 7 Replies View Related

Determine Table Names And Column Names At Runtime?

Jan 22, 2004

Hi

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

Thanks.

View 5 Replies View Related

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

AdventureWorks, 'invalid Object Name' Error

Jul 24, 2007

Hi, I will start step by step:1. a new web site with VS 2005.2. I added a sqldatasouce and connect with AdventureWorks sample database ,which comes with sql server 2005 developer edition, selected by drop-down list. [ server name:(local) ]3. Test connection. It is OK.4. Saved as 'AdventureWorksConnectionString'.5. Some columns are selected in the 'product' table.6. At the end while testing query with 'test query' button it gives:    "There was an error executing the query. Please check the syntax of the command and if present, the types and values of parameters and ensure the are correct.      Invalid object name 'Product'. "7. However when I choice NorthWind database sample I installed externally, there is no problem.Moreover, when I choice AWBuildVersion table in the AdventureWorks, and it's columns, there is also no problem.8. I compared NorthWind and AdventureWorks security properties in the SQL server managment studio, but can't find any differences.9. I have been searching all the web since two days.10. Thanks. 

View 4 Replies View Related

Table Column Names = Dataset Column Values?!

Apr 28, 2008



I need to create the following table in reporting services



PRODUCT April March Feb

2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6





My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???


Thanks in advance!!!

View 1 Replies View Related

Transact SQL :: Get Table And Column Name In Separate Column Using PIVOT

Jul 16, 2015

Is there a way we can get Table and Column name in separate column using PIVOT or something?Right now what i have is:

Text                                                     QueryPlan             Plan_handle  
         Name         Value

select id,name,Address from person     <showPlznXML...   010101                 Table            Person
select id,name,Address from person     <showPlznXML...   010101                 column         id
select id,name,Address from person     <showPlznXML...   010101                 Table            Person

[code]....

View 26 Replies View Related

Know Ing The Table Names When I Know Column Name

May 30, 2008

Hi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists. 
 
 
 
 

View 2 Replies View Related

Get Column Names From Table

Nov 15, 2005

Hi All,

I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.

Thanks a bunch.

Val

View 5 Replies View Related

Get All Table Names With A Given Column Name

Jul 11, 2006

All, is there a way of getting all table names that contain a columnname?I'm looking at a DB that has 125+ tables and I'm interested in findingall table names that contain the column order_date.How can I do it?TIA,Sashi

View 2 Replies View Related

Prepending Column Names By Table

Oct 4, 2006

I wasn't sure whether or not to post this in the .NET or MsSQL forum, mods please move accordingly.

Say I have two tables: Store, and Area that have the following columns:
Area:
AreaID, AreaName, Description

Store:
StoreID, AreaID, StoreName, Description

When I run the following query :
SELECT * FROM Area, Store WHERE Area.AreaID = Store.AreaID, the following columns come back from the .NET SQLDataReader

AreaID, AreaName, Description, StoreID, AreaID, StoreName, Description

Even though the Description fields are different there is no differentiation between which one is which (other than an integer index)

Is there a way to form the SQL query so the columns come back
Area.AreaID, Area.AreaName, Area.Description etc...

I was hoping to do something like the following
Select Area.* as 'Area.'.* ...

but that wasn't liked by the SQL server, any ideas?

Note: The actual tables are more complex than this, and the table names aren't static. Individually aliasing columns won't work, nor will checking the alias.

-MBirchmeier

View 3 Replies View Related

How To Get Column Names Against Each Record In A Table

May 18, 2012

I have a table with almost 100 columns. Ex:

ID ColA ColB ColC
2 Null Word Excel
3 Access Paint Null

Expected result set is :

ID ColA ColB ColC ColumnNames IsEmpty

2 Null Word
Excel ColA
Yes

2 Null Word
Excel ColB
No

2 Null Word
Excel ColC
No
3 Access Paint Null ColA
No

3 Access Paint Null ColB
No
3 Access Paint Null ColC
Yes

I would like to list all the column names against each record of the table and and would like to keep track whether the column is empty or nonempty. How can I write SQL query for this?

View 3 Replies View Related

Extracting Sql Table Column Names

Feb 5, 2006

I am using the following to extract the column names of a table. I would like to do this for the whole database. Currently I am cutting the results into an excel spread. Is there a better way of doing this? Here is the query



SELECT name
FROM syscolumns
WHERE [id] = OBJECT_ID('tablename')

View 4 Replies View Related

Transact SQL :: Get Table And Column Names From XML

Jul 6, 2015

I am trying to find the Table and column names from the below.

Is there a way i can get table name and column name from query_plan column?

SELECT TOP 100  text, query_plan,cp.plan_handle
FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
WHERE objtype='Adhoc'

View 5 Replies View Related

Transact SQL :: Getting Table And Column Names

Jul 21, 2015

 SELECT TOP 100  text, query_plan, cp.plan_handle, qs.last_execution_time
FROM sys.dm_exec_cached_plans cp 
       CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
       CROSS APPLY sys.dm_exec_query_plan(cp.plan_handle)
  JOIN sys.dm_exec_query_stats qs ON cp.plan_handle = qs.plan_handle
WHERE objtype='Adhoc'

I have below output:

Text      Query_plan     Plan_handle     Lst_execution_time
Select id,name from person                  
<Showplan...   dshkkgdaHqrqe13232423    2015-07-21 10:50:22.713
Update customer set Cid=3 where name='abc'      
<Showplan...    poasfvrqe13232423    2015-07-21 10:16:22.500
delete from orders where  ORid=8    
<Showplan...     2ase2423     2015-07-21 10:10:22.710
Select 1,2,3,4,5 from num  
<showplan            afqfqfq   2015-07-21 10:10:22.710
 
I am looking for 

Text           Query_plan                   Plan_handle      Last_execution_time        TabName colname
Select id,name from person  <Showplan... dshkkgdaHqrqe13232423   2015-07-21 10:50:22.713  
Person Id, name Update customer set Cid=3 where name='abc'  
<Showplan   poasfvrqe13232423   2015-07-21 10:16:22.500  customer  Cid,  name
delete from orders where  ORid=8   <Showplan...   2ase2423     2015-07-21 06:10:22.710    Orders    ORid
Select 1,2,3,4,5 from num <showplan            afqfqfq   2015-07-21 10:10:22.710        nUM      1,2,3,4,5

View 2 Replies View Related

Sort COLUMN NAMES Of A Table

Apr 25, 2008

Hi all,

Is there a way i can write a script that will sort the column names of my table?

Example:

Table 1 columns:
First_Name
Last_Name
Address
City
Country


I want to write a script that will sort them like this:

Address
City
Country
First_Name
Last_Name

View 4 Replies View Related

List Column Names Of Sql Table

Mar 20, 2008

I know it is basics, just slipped out of my mind, How do we list or print the columns names of table in sql server 2000.
thanks,

View 10 Replies View Related

Use Column Names And Not Positions When Inserting From One Table Into Another

Jul 20, 2005

/*Code below raises following errors:Server: Msg 245, Level 16, State 1, Line 6Syntax error converting the varchar value 'a' to a column of data typeint.*/create table #x (i integer, c char(1))create table #y (c char(1), i integer)insert into #x VALUES (1, 'a')insert into #y SELECT * from #xdrop table #xdrop table #y--CODE COMPLETEIs there a way to use the column names as the basis for the insert asopposed to column position? The key here is that I DO NOT want toSPECIFY COLUMN NAMES (i.e. 'insert into #y (i,c) SELECT * from #x')Regards,Ty

View 3 Replies View Related

List Table Names In A Database Having A Particular Column.

Aug 3, 2007

Can anybody tell me how to find out whether a particular column exists in any of the tables of a database and if it does, display the table names?


Thanks

View 3 Replies View Related

How To Determine Table And Column Names In A Trigger?

May 5, 2008

Hi all,

I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.

I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.


Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?

Thanks,
-Mike

View 4 Replies View Related

SQL Statement Not Producing Any Error For Invalid Column In A Table

Sep 2, 2006

Hi,

I am using SQL Server 2005 with SP1 patch update.I have tow tables

X table fields:

ClientID,ClientName,ClientRegisteredNumber,HoldingName,HoldingRegisteredNumber,NumberOfHoldings

Y table fields:

ClientID,ClientName,RegisteredNumber,HoldingName,HoldingRegisteredNumber,NumberOfHoldings

If i run a query for X table:

SELECT RegisteredNumber FROM X it produces the error like this

Msg 207, Level 16, State 1, Line 1

Invalid column name 'RegisteredNumber'.



But if i run the query for X,Y table:

SELECT * FROM Y WHERE RegisteredNumber NOT IN

(SELECT RegisteredNumber FROM X)

It's not producing any errors.

Why this? Is this the SQL Bug or my query problem?

Can anyone explain how to solve this?

Balaji

View 3 Replies View Related

PIVOT TABLE Dynamic Column Header?

Nov 14, 2007

I am trying to work on a database with 3 tables. To make it easier I have created a couple of temp tables to work out the syntax.

CREATE TABLE #owner
(
[NameId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)

INSERT INTO #owner VALUES ('ME');
INSERT INTO #owner VALUES ('Other');

CREATE TABLE #propertyType
(
[TypeId] tinyint IDENTITY(1,1) NOT NULL,
[Name] varchar(50) NOT NULL
)

INSERT INTO #propertyType VALUES ('Home');
INSERT INTO #propertyType VALUES ('Car');

CREATE TABLE #property
(
[NameId] tinyint NOT NULL,
[TypeId] tinyint NOT NULL,
[Value] varchar(50) NOT NULL
)

INSERT INTO #property VALUES (1,1, 'Blue');
INSERT INTO #property VALUES (1,2, 'Black');
INSERT INTO #property VALUES (2,1, 'Red');
INSERT INTO #property VALUES (2,2, 'Black');

DROP TABLE #owner;
DROP TABLE #propertyType;
DROP TABLE #property

| NameId | Name |
| 1 | ME|
| 2 | other |

| TypeId | Name |
| 1 | Home |
| 2 | Car |

| NameId | TypeId | Value |
| 1 | 1 | Blue |
| 1 | 2 | Black |
| 2 | 1 | Red |
| 2 | 2 | Black |

Where property value is some arbitrary detail. The real propertyType has 50 or 60 rows and not every property has all of the values. I am trying to create a pivot table that would look like so that I can present the data in an easier to understand format:

[Owner | Home | Car ]
[ME | Blue | Black ]
[Other| Red | Black ]

The propertyTypes are added often, and I don't really have the ability to change them. There is a unique constrant on property on nameid and typeid so there will never be two of the same property with the same owner. Any help would be very helpful.

View 9 Replies View Related

How Count Column In Pivot Table- And Add Result Row

Jan 20, 2008

how count column in pivot table- and add result row
i need to calculate each column
for example
day1 day2 day3 day4 day5
-------------------------------------------------------------------------
1 2 1 2 3
1 2 3 2 2
2 3 2 1 2
2 3 0 0 0
-----------------------------------------------------------new result row
ok ok 1|2|3 1 3

i need to check each column
if i have twice each number
if not show the missing number
TNX




Code Block
DECLARE @Employee TABLE (ID INT, Date SMALLDATETIME, ShiftID TINYINT)
DECLARE @WantedDate SMALLDATETIME, -- Should be a parameter for SP
@BaseDate SMALLDATETIME,
@NumDays TINYINT
SELECT @WantedDate = '20080301', -- User supplied parameter value
@BaseDate = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', @WantedDate), '19000101'),
@NumDays = DATEDIFF(DAY, @BaseDate, DATEADD(MONTH, 1, @BaseDate))

IF @Numdays > 28
BEGIN
SELECT p.ID,
p.[1] , p.[2],p.[3], p.[4], p.[5], p.[6], p.[7], p.[8], p.[9], p.[10], p.[11],
p.[12], p.[13], p.[14], p.[15], p.[16], p.[17], p.[18], p.[19], p.[20], p.[21],
p.[22], p.[23], p.[24], p.[25], p.[26], p.[27], p.[28], p.[29], p.[30], p.[31]
FROM (
SELECT ID,
DATEPART(DAY, Date) AS theDay,
ShiftID
FROM v_Employee
WHERE Date >= @BaseDate
AND Date < DATEADD(MONTH, 1, @BaseDate)
) AS y
PIVOT (
min(y.ShiftID) FOR y.theDay IN ([1], [2], [3], [4], [5], [6], [7],[8] , [9], [10], [11],
[12], [13], [14], [15], [16], [17], [18], [19], [20], [21],
[22], [23], [24], [25], [26], [27], [28], [29], [30], [31])
) AS p
END

View 12 Replies View Related

Variables As Table Or Column Names In A Stored Procedure

Apr 16, 2008

I would like to use variables to set the table name and some column names of a SQL Query in a stored procedure (the variable values will come from a webpage)... something like this:ALTER PROCEDURE dbo.usp_SelectWorkHours
@DayName varchar,@DayIDName varchar
AS
BEGINSELECT @DayName.InTime1, @DayName.OutTime1, @DayName.InTime2, @DayName.OutTime2 FROM @DayName
INNER JOINWorkHours ON @DayName.@DayIDName = @DayName.@DayIDName
INNER JOINEmployees ON WorkHours.WorkHoursID = Employees.WorkHoursID
END
RETURN
...is this possible?? if so how?
Thanks

View 2 Replies View Related

SQL Question: Change Column Names Of Temp Table In SP

Apr 21, 2004

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

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

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

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

Cheers

View 4 Replies View Related

SELECT Statement To Return Table Column Names

Aug 16, 2004

Is there a SELECT statement that will return a the column names of a given table?

View 5 Replies View Related

Exporting Sql Table Into Csv Format Retaining The Column Names

Jul 23, 2005

HiI have been working since 2 days to device a method to export sql tableinto csv format. I have tried using bcp with format option to keep thecolumn names but I'm unable to transfer the file with column names. andalso I'm having problems on columns having decimal data.Can any one suggest me how to automate data transfer(by using SP) andretaining column names.ThanksNoor

View 1 Replies View Related

Ambiguous Column Names In Multi-Table Join

Jun 21, 2006

Hi all,A (possibly dumb) question, but I've had no luck finding a definitiveanswer to it. Suppose I have two tables, Employees and Employers, whichboth have a column named "Id":Employees-Id-FirstName-LastName-SSNetc.Employers-Id-Name-Addressetc.and now I perform the following join:SELECT Employees.*, Employers.*FROM Employees LEFT JOIN Employers ON (Employees.Id=Employers.Id)The result-set will contain two "Id" columns, so SQL Server willdisambiguate them; one column will still be called "Id", while theother will be called "Id1." My question is, how are you supposed toknow which "Id" column belongs to which table? My intuition tells me,and limited testing seems to indicate, that it depends on the order inwhich the table names show up in the query, so that in the aboveexample, "Id" would refer to Employees.Id, while "Id1" would refer toEmployers.Id. Is this order guaranteed?Also, why does SQL Server use such a IMO brain-damaged technique tohandle column name conflicts? In MS Access, it's much morestraightforward; after executing the above query, you can use"Employees.Id" and "Employers.Id" (and more generally,"TableNameOrTableAlias.ColumnName") to refer to the specific "Id"column you want, instead of "Id" and "Id1" -- the"just-tack-on-a-number" strategy is slightly annoying when dealing withcomplex queries.--Mike S

View 6 Replies View Related







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