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
ADVERTISEMENT
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Aug 16, 2004
Is there a SELECT statement that will return a the column names of a given table?
View 5 Replies
View Related
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
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
Sep 11, 2015
how to find the columns names in a table in sql 2008r2.
as i need to compare the midsing fields in the table from two database.
View 3 Replies
View Related
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
May 29, 2015
I have a SQL text column from SP_who2 in table #SqlStatement:
like 1row shown below :
"update Panel set PanelValue=7286 where PanelFirmwareID=4 and PanelSettingID=9004000"
I want to find what table and column names are in the text ..
I tried like below ..
Select B.Statement from #sp_who2 A
LEFT JOIN #SqlStatement B ON A.spid = B.spid
where B.Statement IN (
SELECT T.name, C.name FROM sys.tables T
JOIN sys.columns C
ON T.object_id=C.object_id
WHERE T.type='U'
)
Something like this : find the column names and tables name
View 18 Replies
View Related
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
Oct 25, 2006
I'm trying to write an SSIS package that exports a table that has changing column names to an excel file. The column names change due to the fact that the table is created by a pivot daily. the only thing I'm missing is the ability to dynamically map the tables' columns to the excel destination. Is this possible?
I read in another thread that
"It is not possible to create packages or new objects within packages using SSIS."
I also read in the books online that "The input and the input columns of the Excel destination have no custom
properties." To me this means that I cannot programmatically create or remove columns in the excel destination. Please tell me I'm wrong.
So, to summarize my research so far. In writing an SSIS package, I cannot programmatically create a new excel destination object and I can't manipulate an existing one. I hope I'm wrong. Can anyone help me? (and please correct any wrong assumptions I may have stated)
View 2 Replies
View Related
Dec 26, 2013
In a stored procedure I dynamically create a temp table by selecting the name of Applications from a regular table. Then I add a date column and add the last 12 months. See attachment.
So far so good. Now I want to update the data in columns by querying another regular table. Normally it would be something like:
UPDATE ##TempTable
SET [columName] = (SELECT SUM(columName)
FROM RegularTable
WHERE FORMAT(RegularTable.Date,'MM/yyyy') = FORMAT(##TempMonths.x,'MM/yyyy'))
However, since I don't know what the name of the columns are at any given time, I need to do this dynamically.
how can I get the column names of a Temp table dynamically while doing an Update?
View 4 Replies
View Related
Sep 11, 2013
I want to update Flag column in second table based on the Adder names.
If the Applicatiion has atleast one AIX and Adder name is UDB then the flag would be True.
If the Application has more the one AIX and Adder names are diferent then the flag would be null.
APpName OS Adder
App1 ||| Windows|||Null
App1 ||| Linux |||UDB
App1 ||| AIX |||UDB
App1 ||| Linux |||Sql
App2 ||| AIX ||| UDB
App2 ||| Windows||| UDB
App2 ||| Linux ||| UDB
App2 ||| AIX ||| UDB
OUTPUT SHOULD BE LOOK LIKE BELOW
APpName OS Adder Flag
App1||| Windows|||Null|||null
App1||| Linux |||UDB |||null
App1||| AIX |||UDB |||null
App1||| Linux |||Sql |||null
App2|||AIX ||| UDB|||TRUE
App2|||Windows||| UDB|||TRUE
App2|||Linux ||| UDB|||TRUE
App2|||AIX ||| UDB|||TRUE
View 5 Replies
View Related
Nov 6, 2007
I thought I saw this done once before. So today I hunted around inBooks OnLine and did a Google search. So far I have found nothingclose. So if you know how to do it, please tell me or if cannot bedone, I'd appreciate know that too.Thanks in advance,IanO
View 2 Replies
View Related
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
Feb 26, 2015
I am using CROSS APPLY instead of UNPIVOT to unpivot > one column. I am wondering if I can dynamically replace column names based on different tables? The example code that I have working is based on the "Allergy" table. I have thirty more specialty tables to go. I'll show the working code first, then an example of another table's columns to show differences:
select [uplift specialty], [member po],[practice unit name], [final nomination status]
,[final uplift status], [final rank], [final uplift percentage]
,practiceID=row_number() over (partition by [practice unit name] order by Metricname)
,metricname,Metricvalue, metricpercentilerank
[code]....
Rheumatology Table:The columns that vary start with "GDR" and [GDR Percentile Rank] so I'm just showing those:
GDR (nvarchar(255), null)
GDR Percentile Rank (nvarchar(255), null)
GDR PGS (nvarchar(255), null)
GDR Rank Number (nvarchar(255), null)
PMPM (nvarchar(255), null)
[Code] ....
These are imported from an Excel Workbook so that's why all the columns with spaces for now.
View 9 Replies
View Related
Aug 5, 2015
I am trying a create views that would join 2 tables:
Table 1: Has all the columns need by a view (
Name: Product
Structure: ID, Attribute 1, Attribute 2, Attribute 3, Attribute 4, Attribute 5 etc
Table 2: Is a lookup table that provides the names of columns
Name: lookupTable
Structure: tableName, ColumnName, columnValue
Values: Product, Attribute1, Color
Product, Attribute2, Size
Product, Attribute3, Flavor
Product, Attribute4, Shape
I want to create a view that looks like
ID, Color, Size, Flavor, Shape
View 4 Replies
View Related
Mar 1, 2015
I am designing a package to export staging tables into a flat file.The names of the tables will be: TableAStaging_YYYYMM and TableBStaging_YYYYMM. As you can see the names of the tables will be changing each month.
The flat files will have similar naming: C:MyPathFlatFileTableAStaging__YYYYMM and C:MyPathFlatFileTableAStaging__YYYYMM.I want to run the package as an sql job in two steps, one for each table.I need to dynamically pass the table names and file names (together with the path) to the IS package.
View 1 Replies
View Related
Jan 21, 2004
I'm trying to do an update query that looks like this:
UPDATE
PAEMPLOYEE
SET PAEMPLOYEE.LOCAT_CODE = EMPLOYEE.PROCESS_LEVEL
FROM
PAEMPLOYEE A
JOIN EMPLOYEE B ON A.EMPLOYEE = B.EMPLOYEE
It's erroring out on the Employee prefix B.EMPLOYEE saying:
..."does not match with a table name or alias name used in the query"
Is it wrong or will it cause problems to have a field name the same as the table name?
View 5 Replies
View Related
